我有这个表,我想把几行的'change‘列的值相加(或者,更准确地说,从'ne’值为零的那一行一直到下一行,'ne‘的值为零(而不是第二行本身))。任何答案都将不胜感激。
┌─rn─┬───────date─┬─ne─┬───────change─┐
│ 0 │ 2008-12-07 │ 0 │ -10330848398 │
│ 1 │ 2009-04-14 │ 1 │ -61290 │
│ 2 │ 2009-04-26 │ 1 │ 9605743360 │
│ 3 │ 2013-07-06 │ 0 │ -32028871920 │
│ 4 │ 2014-01-12 │ 1 │ -42296164902 │
│ 5 │ 2015-06-08 │ 1 │ 59100383646 │
└────┴────────────┴────┴──────────────┘我们期望的结果是这样的。
row start end sum(change)
--------------------------------------------------
0 | 2008-12-07 | 2009-04-26 | -725,166,328
--------------------------------------------------
1 | 2013-07-06 | 2015-06-08 | -15,224,653,176
--------------------------------------------------发布于 2020-04-12 11:24:41
这在bigdata (>1亿行)中是无法解决的
SELECT
d[1] AS s,
d[-1] AS e,
arraySum(c) AS sm
FROM
(
SELECT
arraySplit((x, y) -> (NOT y), d, n) AS dd,
arraySplit((x, y) -> (NOT y), c, n) AS cc
FROM
(
SELECT
groupArray(date) AS d,
groupArray(ne) AS n,
groupArray(change) AS c
FROM
(
SELECT *
FROM mytable
ORDER BY rn ASC
)
)
)
ARRAY JOIN
dd AS d,
cc AS c
┌─s──────────┬─e──────────┬───────────sm─┐
│ 2008-12-07 │ 2009-04-26 │ -725166328 │
│ 2013-07-06 │ 2015-06-08 │ -15224653176 │
└────────────┴────────────┴──────────────┘发布于 2020-04-12 05:19:11
这是一个差距和岛屿问题。规范的解决方案确实使用了窗口函数,就Clickhouse而言,它并不支持。
以下是使用子查询模拟条件窗口和的一种方法:
select
min(date) start_date,
max(date) end_date,
sum(change) sum_change
from (
select
t.*,
(select count(*) from mytable t1 where t1.date <= t.date and t1.ne = 0) grp
from mytable t
) t
group by grp子查询计算从表的第一行到当前行有多少行具有ne = 0。这定义了记录组。那么剩下的工作就是聚合了。
如果您可以使用窗口函数,您可以将其表述为:
select
min(date) start_date,
max(date) end_date,
sum(change) sum_change
from (
select
t.*,
sum(case when ne = 0 then 1 else 0 end) over(order by date) grp
from mytable t
) t
group by grp发布于 2020-04-13 14:06:48
这是解决这个问题的另一种方法:
WITH (SELECT arraySort(groupArray(rn))
FROM test_table
WHERE ne = 0) as group_start_id
SELECT argMin(date, rn) start, argMax(date, rn) end, sum(change)
FROM (
SELECT rn, date, change
FROM test_table
ORDER BY rn)
GROUP BY arrayFirstIndex(x -> rn < x, group_start_id)
ORDER BY start对样本数据进行测试:
WITH (SELECT arraySort(groupArray(rn))
FROM (
SELECT data.1 rn, data.2 date, data.3 ne, data.4 change
FROM (
SELECT arrayJoin([
(0, toDate('2008-12-07'), 0, toInt64(-10330848398)),
(1, toDate('2009-04-14'), 1, toInt64(-61290)),
(2, toDate('2009-04-26'), 1, toInt64(9605743360)),
(3, toDate('2013-07-06'), 0, toInt64(-32028871920)),
(4, toDate('2014-01-12'), 1, toInt64(-42296164902)),
(5, toDate('2015-06-08'), 1, toInt64(59100383646)),
(6, toDate('2015-06-08'), 0, toInt64(101)),
(7, toDate('2015-06-09'), 0, toInt64(102)),
(8, toDate('2015-06-10'), 0, toInt64(103)),
(9, toDate('2015-06-11'), 1, toInt64(104))
]) data))
WHERE ne = 0) as group_start_id
SELECT argMin(date, rn) start, argMax(date, rn) end, sum(change)
FROM (
SELECT data.1 rn, data.2 date, data.4 change
FROM (
SELECT arrayJoin([
(0, toDate('2008-12-07'), 0, toInt64(-10330848398)),
(1, toDate('2009-04-14'), 1, toInt64(-61290)),
(2, toDate('2009-04-26'), 1, toInt64(9605743360)),
(3, toDate('2013-07-06'), 0, toInt64(-32028871920)),
(4, toDate('2014-01-12'), 1, toInt64(-42296164902)),
(5, toDate('2015-06-08'), 1, toInt64(59100383646)),
(6, toDate('2015-06-08'), 0, toInt64(101)),
(7, toDate('2015-06-09'), 0, toInt64(102)),
(8, toDate('2015-06-10'), 0, toInt64(103)),
(9, toDate('2015-06-11'), 1, toInt64(104))
]) data)
ORDER BY rn)
GROUP BY arrayFirstIndex(x -> rn < x, group_start_id)
ORDER BY start
/* result
┌──────start─┬────────end─┬──sum(change)─┐
│ 2008-12-07 │ 2009-04-26 │ -725166328 │
│ 2013-07-06 │ 2015-06-08 │ -15224653176 │
│ 2015-06-08 │ 2015-06-08 │ 101 │
│ 2015-06-09 │ 2015-06-09 │ 102 │
│ 2015-06-10 │ 2015-06-11 │ 207 │
└────────────┴────────────┴──────────────┘
*/https://stackoverflow.com/questions/61163259
复制相似问题