如何对几行的列值求和? [英] How to sum the values of a column for several rows?

问题描述

我有此表，我想为几行添加'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 │
└────────────┴────────────┴──────────────┘

这篇关于如何对几行的列值求和?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！