Вопрос:

SQL Windowing accumulative sum with grouping

sql aggregate presto windowing

30 просмотра

1 ответ

1227 Репутация автора

I've got a table like this

|week_no|value|attribute|
-------------------------
|   1   |  3  |    a    |
|   2   |  3  |    a    |
|   3   |  3  |    a    |
|   1   |  4  |    b    |
|   2   |  4  |    b    |
|   3   |  4  |    b    |

I'd like to have an accumulative account of the value column

|week_no|value|attribute|accum_value|
-------------------------------------
|   1   |  3  |    a    |     3     |
|   2   |  3  |    a    |     6     |
|   3   |  3  |    a    |     9     |
|   1   |  4  |    b    |     4     |
|   2   |  4  |    b    |     8     |
|   3   |  4  |    b    |    12     |

I've attempted doing the above by using this windowing function though it doesn't seem to be returning the correct values

SUM(value) OVER(ORDER BY 1 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS accum_value
Автор: edumike Источник Размещён: 12.04.2017 01:48

Ответы (1)


1 плюс

854539 Репутация автора

The correct window function would use partition by:

SUM(value) OVER (PARTITION BY attribute ORDER BY week_no
                 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                ) AS accum_value
Автор: Gordon Linoff Размещён: 12.04.2017 02:21
Вопросы из категории :
32x32