Создать таблицу, в которой хранится остаток очков каждого клиента на начало каждого последующего месяца

Question

Нужна помощь: создать таблицу из .csv , с сортировкой по месяцам суммы очков на начало месяца.(Итогового на следующий, как в примере). Итог на конец апреля фиксируется 2020-05-01.

Пример:

+---------------------+------------+------------+--------+
|       period        | recordkind | customerid | points |
+---------------------+------------+------------+--------+
| 2020-04-03 11:06:25 |          0 |          1 |     14 |
| 2020-04-14 12:34:30 |          0 |          2 |      5 |
| 2020-04-16 11:06:15 |          0 |          2 |      5 |
| 2020-04-30 11:34:50 |          1 |          1 |      6 |
| 2020-05-07 14:27:52 |          0 |          1 |    300 |
| 2020-05-08 16:36:58 |          1 |          1 |     68 |
| 2020-05-12 19:30:43 |          0 |          2 |     12 |
| 2020-05-27 09:46:14 |          0 |          2 |      2 |
+---------------------+------------+------------+--------+

Результат:

+---------------------+------------+-----------+
|       period        | customerid | sumpoints |
+---------------------+------------+-----------+
| 2020-05-01 00:00:00 |          1 |         8 |
| 2020-05-01 00:00:00 |          2 |        10 |
| 2020-06-01 00:00:00 |          1 |       240 |
| 2020-06-01 00:00:00 |          2 |        24 |
+---------------------+------------+-----------+

Надеюсь на помощь в реализации.

Answer 1

Используйте оконную функцию LAG. Вариант для SQLite:

WITH t_2 AS (
  SELECT DATE(period, 'start of month') AS month
       , customerid
       , SUM(
           CASE WHEN recordkind = 0 THEN points ELSE -1 * points END
         ) AS month_total
    FROM t_1
   GROUP BY DATE(period, 'start of month'), customerid
)
SELECT DATE(month, '+1 month') AS next_month
     , customerid
     , month_total + LAG(month_total, 1, CAST(0 AS BIGINT)) OVER (
         PARTITION BY customerid
             ORDER BY month, customerid
       ) AS running_total
  FROM t_2
 ORDER BY month, customerid
;

Вариант для PostgreSQL:

WITH t_2 AS (
  SELECT DATE_TRUNC('month', period) AS month
       , customerid
       , SUM(
           CASE WHEN recordkind = 0 THEN points ELSE -1 * points END
         ) AS month_total
    FROM t_1
   GROUP BY DATE_TRUNC('month', period), customerid
)
SELECT TO_CHAR(month + INTERVAL '1 month', 'YYYY-MM-DD') AS next_month
     , customerid
     , month_total + LAG(month_total, 1, CAST(0 AS BIGINT)) OVER (
         PARTITION BY customerid
             ORDER BY month, customerid
       ) AS running_total
  FROM t_2
 ORDER BY month, customerid
;

БЛОГ НА HUSL

Создать таблицу, в которой хранится остаток очков каждого клиента на начало каждого последующего месяца

Ответы (1 шт):