Блог

У меня есть таблица с именем user_state со столбцами user_ID, account_id, Balance, Date . У каждого идентификатора пользователя может быть несколько учетных записей. Пример таблицы выглядит следующим образом: https://i.stack.imgur.com/w4odf.png вы можете создать таблицу с помощью скрипта:

     CREATE TABLE USER_STATE (USER_ID int, ACCOUNT_ID int, SNAPSHOT_DATE DATE, BALANCE float);
    INSERT INTO USER_STATE(USER_ID,ACCOUNT_ID,SNAPSHOT_DATE,BALANCE) VALUES(23,1 ,'2016-07-01', 50);
    INSERT INTO USER_STATE(USER_ID,ACCOUNT_ID,SNAPSHOT_DATE,BALANCE) VALUES(23,2 ,'2016-07-01', 50);
    INSERT INTO USER_STATE(USER_ID,ACCOUNT_ID,SNAPSHOT_DATE,BALANCE) VALUES(23,2 ,'2016-07-05', 80);
    INSERT INTO USER_STATE(USER_ID,ACCOUNT_ID,SNAPSHOT_DATE,BALANCE) VALUES(23,1, '2016-07-27', 150);
    INSERT INTO USER_STATE(USER_ID,ACCOUNT_ID,SNAPSHOT_DATE,BALANCE) VALUES(23,1,'2016-07-31', 200);
    INSERT INTO USER_STATE(USER_ID,ACCOUNT_ID,SNAPSHOT_DATE,BALANCE) VALUES(23,1, '2016-08-18', 150);
    INSERT INTO USER_STATE(USER_ID,ACCOUNT_ID,SNAPSHOT_DATE,BALANCE) VALUES(23,2,'2016-08-21', 250);
    INSERT INTO USER_STATE(USER_ID,ACCOUNT_ID,SNAPSHOT_DATE,BALANCE) VALUES(23,1,'2016-08-21', 250);
    INSERT INTO USER_STATE(USER_ID,ACCOUNT_ID,SNAPSHOT_DATE,BALANCE) VALUES(44,1, '2016-06-01', 10);
    INSERT INTO USER_STATE(USER_ID,ACCOUNT_ID,SNAPSHOT_DATE,BALANCE) VALUES(44,2, '2016-06-01', 20);
    INSERT INTO USER_STATE(USER_ID,ACCOUNT_ID,SNAPSHOT_DATE,BALANCE) VALUES(44, 1,'2016-06-05', 40);
    INSERT INTO USER_STATE(USER_ID,ACCOUNT_ID,SNAPSHOT_DATE,BALANCE) VALUES(44,1, '2016-06-27', 90);
    INSERT INTO USER_STATE(USER_ID,ACCOUNT_ID,SNAPSHOT_DATE,BALANCE) VALUES(44,1, '2016-06-31', 300);
    INSERT INTO USER_STATE(USER_ID,ACCOUNT_ID,SNAPSHOT_DATE,BALANCE) VALUES(44,1, '2016-09-18', 400);
    INSERT INTO USER_STATE(USER_ID,ACCOUNT_ID,SNAPSHOT_DATE,BALANCE) VALUES(44,1, '2016-09-21', 200);
    INSERT INTO USER_STATE(USER_ID,ACCOUNT_ID,SNAPSHOT_DATE,BALANCE) VALUES(44,2, '2016-09-21', 200);
  

Я хотел бы получить эти значения в месяц для каждого пользователя:

• first_date месяца и sum_balance за первое число месяца для каждого пользователя.
• last_date месяца и sum_balance для последней даты этого месяца для каждого пользователя.
• ave_balance за каждый месяц для каждого пользователя.

Конечный результат должен выглядеть так: https://i.stack.imgur.com/Fyjes.png

Одно из решений — получить элемент, который я перечислил, и который мне нужен в месяц для каждого пользователя отдельно, и объединить таблицы, есть ли более эффективное и простое решение для этого?

Спасибо, Бикрам

Хммм . . . агрегируйте по дате моментального снимка и пользователю, а затем по пользователю и месяцу:

 select user_id, date_trunc(snapshot_date, month) as yyyymm,
       min(snapshot_date) as first_date,
       array_agg(balance order by snapshot_date limit 1)[ordinal(1)] as first_balance,
       max(snapshot_date) as first_date,
       array_agg(balance order by snapshot_date desc limit 1)[ordinal(1)] as last_balance,
       avg(balance)
from (select user_id, snapshot_date, sum(balance) as balance
      from user_state u
      group by 1, 2
     ) u
group by 1, 2;