Блог

У меня есть этот набор данных, в котором есть даты и продукты для городов:

 CREATE TABLE my_table (
    the_id varchar(5) NOT NULL, 
    the_date timestamp NOT NULL,
    the_city varchar(5) NOT NULL,
    the_product varchar(1) NOT NULL
);

INSERT INTO my_table
VALUES ('VIS01', '2019-05-02 09:00:00','LISBO','A'), 
       ('VIS02', '2019-05-04 12:00:00','EVORA','A'), 
       ('VIS03', '2019-05-05 18:00:00','LISBO','B'), 
       ('VIS04', '2019-05-06 18:30:00','PORTO','B'), 
       ('VIS05', '2019-05-15 12:05:00','PORTO','C'), 
       ('VIS06', '2019-06-02 18:06:00','EVORA','C'),
       ('VIS07', '2019-06-02 18:07:00','PORTO','A'),
       ('VIS08', '2019-06-04 18:08:00','EVORA','B'),
       ('VIS09', '2019-06-07 18:09:00','LISBO','B'),
       ('VIS10', '2019-06-09 18:10:00','LISBO','D'),
       ('VIS11', '2019-06-12 18:11:00','EVORA','D'),
       ('VIS12', '2019-06-15 18:12:00','LISBO','E'),
       ('VIS13', '2019-06-15 18:13:00','EVORA','F'),
       ('VIS14', '2019-06-18 18:14:00','PORTO','G'),
       ('VIS15', '2019-06-23 18:15:00','LISBO','A'),
       ('VIS16', '2019-06-25 18:16:00','LISBO','A'),
       ('VIS17', '2019-06-27 18:17:00','LISBO','F'),
       ('VIS18', '2019-06-27 18:18:00','LISBO','A'),
       ('VIS19', '2019-06-28 18:19:00','LISBO','A'),
       ('VIS20', '2019-06-30 18:20:00','EVORA','D'),
       ('VIS21', '2019-07-01 18:21:00','EVORA','D'),
       ('VIS22', '2019-07-04 18:30:00','EVORA','D'),
       ('VIS23', '2019-07-04 18:31:00','EVORA','B'),
       ('VIS24', '2019-07-06 18:40:00','EVORA','K'),
       ('VIS25', '2019-07-12 18:50:00','EVORA','G'),
       ('VIS26', '2019-07-15 18:00:00','PORTO','C'),
       ('VIS27', '2019-07-18 18:00:00','PORTO','C'),
       ('VIS28', '2019-07-25 18:00:00','PORTO','B'),
       ('VIS29', '2019-07-30 18:00:00','PORTO','M');
 

И я хочу получать два лучших в месяц. Ожидаемый результат должен быть:

 month    product    count
2019-05  A          2
2019-05  B          2
2019-06  A          5
2019-06  D          3
2019-07  C          2
2019-07  D          2
 

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

Во-первых, вы можете использовать to_char(the_date,'YYYY-MM') , чтобы получить год и месяц в правильном формате.

Затем вы можете использовать count(*) для группировки по месяцам и продуктам, а row_number() также для присвоения порядкового номера каждой строке в группах.

 SELECT to_char(the_date,'YYYY-MM') as month,
       the_product as product,
       count(*) as p_count,
       row_number() over (partition by to_char(the_date,'YYYY-MM') order by count(*) desc) as seq
FROM my_table
group by month, product
 

Наконец, вы можете обернуть это во внешний запрос, чтобы выбрать только те столбцы и строки, которые вам нужны.

 SELECT month, product, p_count as count
FROM (
    SELECT to_char(the_date,'YYYY-MM') as month,
           the_product as product,
           count(*) as p_count,
           row_number() over (partition by to_char(the_date,'YYYY-MM') order by count(*) desc) as seq
    FROM my_table
    group by month, product
) as foo
where foo.seq <= 2;
 

Вы можете использовать функции агрегирования и окна:

 select mp.*
from (select date_trunc('month', the_date) as yyyymm,
             the_product, count(*) as cnt,
             row_number() over (partition by date_trunc('month', the_date) order by count(*) desc) as seqnum
      from my_table
      group by yyyymm, the_product
     ) mp
where seqnum <= 2;
 

В postgresql, я полагаю, вы можете извлечь каждую часть метки времени с помощью Extract функции. например:

 SELECT the_date, EXTRACT(MONTH from the_date) as MONTH
 

тем не менее, вы можете затем сгруппировать по продуктам, затем по месяцам и выбрать 2 ЛУЧШИХ

 SELECT EXTRACT(MONTH from the_date) as month, the_product, count (*) FROM my_table
GROUP BY EXTRACT(MONTH from the_date), the_product
ORDER BY count(*)
LIMIT 2
 

Возможно, потребуется провести некоторую оптимизацию, так как у меня нет базы данных для тестирования запроса, но это может дать вам хорошее начало