如何对几行的列值求和?
问题描述：
我有此表，我想为几行添加'change'列的值(或者，更确切地说，从'ne'值为零的行到下一个包含零的行)"ne"(不是第二个本身).任何答案将不胜感激.
I have this table, and I want to add the values of 'change' column for several rows (Or, to be more exact from the row in which 'ne' value is zero up to the next row which includes zero for 'ne' (not the second one itself)). Any answer would be appreciated.
┌─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 │
└────┴────────────┴────┴──────────────┘
我们期望的结果是这样的.
The result we expect is something like this.
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
--------------------------------------------------
答
在大数据(> 1亿行)中无法解决
it's unsolvable in bigdata ( > 100 millions rows)
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 │
└────────────┴────────────┴──────────────┘