Блог

я застрял с запросом на подсчет идентификатора, где, если он существует в предыдущем месяце, чем 1

моя таблица выглядит следующим образом

 date      |      id       |
2020-02-02|       1       |
2020-03-04|       1       |
2020-03-04|       2       |
2020-04-05|       1       |
2020-04-05|       3       |
2020-05-06|       2       |
2020-05-06|       3       |
2020-06-07|       2       |
2020-06-07|       3       |
 

я застрял с этим запросом

 SELECT date_trunc('month',date), id
FROM table
WHERE id IN 
(SELECT DISTINCT id FROM table WHERE date 
BETWEEN date_trunc('month', current_date) - interval '1 month' AND date_trunc('month', current_date)
 

основная проблема в том, что я застрял с функцией current_date. существуют ли какие-либо динамические способы изменения current_date? Спасибо

Я ожидал, что мой результат будет

 date      |      count    |
2020-02-01|       0       |
2020-03-01|       1       |
2020-04-01|       1       |
2020-05-01|       1       |
2020-06-01|       2       |
 

Решение 1 с САМОСОЕДИНЕНИЕМ

 SELECT date_trunc('month', c.date) :: date AS date
     , count(DISTINCT c.id) FILTER (WHERE p.date IS NOT NULL)
  FROM test AS c
  LEFT JOIN test AS p
    ON c.id = p.id
   AND date_trunc('month', c.date) = date_trunc('month', p.date)   interval '1 month'
 GROUP BY date_trunc('month', c.date)
 ORDER BY date_trunc('month', c.date)
 

Результат :

 date        count
2020-02-01  0
2020-03-01  1
2020-04-01  1
2020-05-01  1
2020-06-01  2
 

Решение 2 с ОКОННЫМИ ФУНКЦИЯМИ

 SELECT DISTINCT ON (date) date
     , count(*) FILTER (WHERE count > 0 AND previous_month) OVER (PARTITION BY date)
  FROM
     ( SELECT DISTINCT ON (id, date_trunc('month', date))
              id
            , date_trunc('month', date) AS date
            , count(*) OVER w AS count
            , first_value(date_trunc('month', date)) OVER w = date_trunc('month', date) - interval '1 month' AS previous_month
         FROM test
       WINDOW w AS (PARTITION BY id ORDER BY date_trunc('month', date) GROUPS BETWEEN 1 PRECEDING AND 1 PRECEDING)
     ) AS a
 

Результат :

 date        count
2020-02-01  0
2020-03-01  1
2020-04-01  1
2020-05-01  1
2020-06-01  2
 

см. dbfiddle