hivesql,在滑动10分钟窗口中查找最大计数

xwbd5t1u  于 2021-05-29  发布在  Hadoop
关注(0)|答案(2)|浏览(1361)

我有一张大致(超级简化)的table

  1. ts | session | other_stuff
  2. ------------------------------
  3. 100 | A | ...
  4. 101 | B | ...
  5. 101 | A | ...
  6. 102 | C | ...
  7. 103 | A | ...
  8. 104 | C | ...
  9. 104 | A | ...
  10. 9999 | D | ...
  11. 20000 | D | ...
  12. ``` `ts` 时间戳存储为双精度。我想找到任何10分钟窗口中活动会话的最大数目。所以在上面的例子中答案是 `3` 因为 `A` , `B` ,和 `C` 都在10分钟内活跃起来。对于这个问题,什么是正确的查询,我尝试了一些方法,但是遇到了错误,所以我肯定没有正确地考虑这个问题。
  13. 我试过了

SELECT *,
(
SELECT COUNT(DISTINCT session)
FROM mytable mi
WHERE mi.ts BETWEEN m.ts - 300 AND m.ts + 300
) AS maxconcurrent
FROM mytable m
ORDER BY
maxconcurrent DESC

  1. 但是收到错误

Presto query has failed. Unexpected node: com.facebook.presto.sql.planner.plan.LateralJoinNode

  1. 编辑:这里有一张表,其中窗口的滑动特性非常重要

ts | session | other_stuff

100 | A | ...
201 | B | ...
301 | A | ...
702 | C | ...
1503 | A | ...
2504 | C | ...
3696 | A | ...
9999 | D | ...
20000 | D | ...

  1. 最大值仍然是3,但现在它是从窗口覆盖201801
xpcnnkqh

xpcnnkqh1#

非常有趣的问题。这是我如何接近它的

  1. select * from sliding
  2. +-------------+----------------+--+
  3. | sliding.ts | sliding.users |
  4. +-------------+----------------+--+
  5. | 100 | A |
  6. | 101 | B |
  7. | 101 | A |
  8. | 102 | C |
  9. | 103 | A |
  10. | 104 | C |
  11. | 104 | A |
  12. | 9999 | D |
  13. | 20000 | D |
  14. +-------------+----------------+--+

我们现在需要计算 ts 当前行的 ts 上一行的 (lag) 或下一行 (lead) . lag(ts,1,0) 它提供了 ts 上一行的。但有一个问题,如果当前行是第一行,会发生什么?没问题,用吧 lag(ts,1,0) 这个 0 如果前面没有行,则返回默认值。
现在我们需要做的就是减去 lagts 并应用条件(您的时间窗口)。也就是说,检查电流 ts - lag_ts 在600以内。
有人可能会认为,如果前一行 ts 远高于当前行 ts ? 但不会,因为 over 子句具有 order by ts .

  1. select users, ts, lag, lead from (
  2. select users, ts,
  3. lag(ts,1,0) over (order by ts) as lag,
  4. lead(ts,1) over( order by ts) as lead
  5. from sliding ) tbl
  6. where (ts - lag) <= 600
  7. +--------+------+------+-------+--+
  8. | users | ts | lag | lead |
  9. +--------+------+------+-------+--+
  10. | A | 100 | 0 | 101 |
  11. | A | 101 | 100 | 101 |
  12. | B | 101 | 101 | 102 |
  13. | C | 102 | 101 | 103 |
  14. | A | 103 | 102 | 104 |
  15. | A | 104 | 103 | 104 |
  16. | C | 104 | 104 | 9999 |
  17. +--------+------+------+-------+--+

应用distinct count gets

  1. select count(distinct users) from (
  2. select users, ts,
  3. lag(ts,1,0) over (order by ts) as lag,
  4. lead(ts,1) over( order by ts) as lead
  5. from sliding ) tbl
  6. where (ts - lag) <= 600
  7. +------+--+
  8. | _c0 |
  9. +------+--+
  10. | 3 |
  11. +------+--+
展开查看全部
w8rqjzmb

w8rqjzmb2#

我不是用户如果配置单元允许窗口有动态的边界宽度,它只支持固定的窗口宽度据我所知
但看看这是否对你有用。使用 floor 舍入最近的10分钟时间戳并做一个分析函数。

  1. select ts, session , count(distinct session) over (partition by floor((ts+599)/600) * 600) from your_table;

结果如下:

  1. ts | session | cnt
  2. ------------------------------
  3. 100 | A | 2
  4. 201 | B | 2
  5. 301 | A | 2
  6. 702 | C | 1
  7. ``` `floor((ts+599)/600) * 600)` -这使得时间戳为0-600的会话将落入一个存储桶,601-1200落入另一个存储桶,以此类推。
  8. 取决于您使用的配置单元版本**不同**部件可能/可能不起作用 `count(distinct session) over (..)`

相关问题