PostgreSQL 쿼리를 사용하여 날짜를 계산 / 그룹화하고 데이터없이 날짜를 표시

PostgreSQL 쿼리를 사용하여 날짜를 계산 / 그룹화하고 데이터없이 날짜를 표시

---

반환하는 PostgreSQL 쿼리를 생성해야합니다.

• 하루
• 그날 발견 된 물체의 수

그날 개체가 발견되지 않더라도 매일 결과에 매일 표시되는 것이 중요합니다 . (이것은 이전에 논의되었지만 특정 사례에서 작동하도록 할 수 없었습니다.)

먼저 조인 할 수있는 일 범위를 생성 하는 SQL 쿼리를 찾았습니다 .

SELECT to_char(date_trunc('day', (current_date - offs)), 'YYYY-MM-DD')
AS date 
FROM generate_series(0, 365, 1) 
AS offs

결과 :

    date    
------------
 2013-03-28
 2013-03-27
 2013-03-26
 2013-03-25
 ...
 2012-03-28
(366 rows)

이제 '생성 된'열이있는 'sharer_emailshare'라는 테이블에이를 조인하려고합니다.

Table 'public.sharer_emailshare'
column    |   type  
-------------------
id        | integer
created   | timestamp with time zone
message   | text
to        | character varying(75)

GROUP BY지금까지 내가 가진 최고의 쿼리 는 다음과 같습니다 .

SELECT d.date, count(se.id) FROM (
    select to_char(date_trunc('day', (current_date - offs)), 'YYYY-MM-DD')
    AS date 
    FROM generate_series(0, 365, 1) 
    AS offs
    ) d 
JOIN sharer_emailshare se 
ON (d.date=to_char(date_trunc('day', se.created), 'YYYY-MM-DD'))  
GROUP BY d.date;

결과 :

    date    | count 
------------+-------
 2013-03-27 |    11
 2013-03-24 |     2
 2013-02-14 |     2
(3 rows)

원하는 결과 :

    date    | count 
------------+-------
 2013-03-28 |     0
 2013-03-27 |    11
 2013-03-26 |     0
 2013-03-25 |     0
 2013-03-24 |     2
 2013-03-23 |     0
 ...
 2012-03-28 |     0
(366 rows)

내가 올바르게 이해한다면 이것은 내가 일반 (묵시적 INNER)을 사용하고 있기 때문이며 JOIN이것은 postgres 문서에서 논의 된 바와 같이 예상되는 동작 입니다.

수십 개의 StackOverflow 솔루션을 살펴 봤는데 쿼리가 작동하는 모든 솔루션이 MySQL / Oracle / MSSQL에 고유 한 것으로 보이며 PostgreSQL로 변환하는 데 어려움을 겪고 있습니다.

이 질문을 한 사람 은 Postgres와 함께 자신의 대답을 찾았지만 얼마 전에 만료 된 pastebin 링크에 넣었습니다.

내가로 전환하려면 시도했습니다 LEFT OUTER JOIN, RIGHT JOIN, RIGHT OUTER JOIN, CROSS JOIN, 용도 CASE널 (null), 경우 다른 값에서 하위에 문을 COALESCE등 기본 가치를 제공하기 위해,하지만 난 내가 원하는 걸 얻는 방법을 사용할 수 없었다.

도움을 주시면 감사하겠습니다! 그리고 곧 거대한 PostgreSQL 책을 읽을 거라고 약속합니다;)

---

left outer join내부 조인 대신 다음이 필요합니다 .

SELECT d.date, count(se.id)
FROM (SELECT to_char(date_trunc('day', (current_date - offs)), 'YYYY-MM-DD') AS date 
      FROM generate_series(0, 365, 1) AS offs
     ) d LEFT OUTER JOIN
     sharer_emailshare se 
     ON d.date = to_char(date_trunc('day', se.created), 'YYYY-MM-DD'))  
GROUP BY d.date;

---

Gordon Linoff의 유용한 답변을 확장하면 다음과 같은 몇 가지 개선 사항을 제안합니다.

• ::date대신 사용date_trunc('day', ...)
• 문자 유형이 아닌 날짜 유형에 참여하십시오 (더 깔끔합니다).
• 나중에 변경하기 쉽도록 특정 기간을 사용하세요. 이 경우 테이블의 가장 최근 항목 이전 1 년을 선택합니다. 다른 쿼리로는 쉽게 수행 할 수없는 작업입니다.
• 임의의 하위 쿼리에 대한 합계를 계산합니다 (CTE 사용). 관심있는 열을 날짜 유형으로 캐스팅하고 date_column이라고 부르면됩니다.
• 누적 합계 열을 포함합니다. (왜 안돼?)

내 질문은 다음과 같습니다.

WITH dates_table AS (
    SELECT created::date AS date_column FROM sharer_emailshare WHERE showroom_id=5
)
SELECT series_table.date, COUNT(dates_table.date_column), SUM(COUNT(dates_table.date_column)) OVER (ORDER BY series_table.date) FROM (
    SELECT (last_date - b.offs) AS date
        FROM (
            SELECT GENERATE_SERIES(0, last_date - first_date, 1) AS offs, last_date from (
                 SELECT MAX(date_column) AS last_date, (MAX(date_column) - '1 year'::interval)::date AS first_date FROM dates_table
            ) AS a
        ) AS b
) AS series_table
LEFT OUTER JOIN dates_table
    ON (series_table.date = dates_table.date_column)
GROUP BY series_table.date
ORDER BY series_table.date

I tested the query, and it produces the same results, plus the column for cumulative total.

---

Based on Gordon Linoff's answer I realized another problem was that I had a WHERE clause that I didn't mention in the original question.

Instead of a naked WHERE, I made a subquery:

SELECT d.date, count(se.id) FROM (
    select to_char(date_trunc('day', (current_date - offs)), 'YYYY-MM-DD')
    AS date 
    FROM generate_series(0, 365, 1) 
    AS offs
    ) d 
LEFT OUTER JOIN (
    SELECT * FROM sharer_emailshare 
    WHERE showroom_id=5
) se
ON (d.date=to_char(date_trunc('day', se.created), 'YYYY-MM-DD')) 
GROUP BY d.date;

---

I'll try to provide an answer that includes some explanation. I'll start with the smallest building block and work up.

If you run a query like this:

SELECT series.number FROM generate_series(0, 9) AS series(number)

You get output like this:

 number 
--------
      0
      1
      2
      3
      4
      5
      6
      7
      8
      9
(10 rows)

This can be turned into dates like this:

SELECT CURRENT_DATE + sequential_dates.date AS date
  FROM generate_series(0, 9) AS sequential_dates(date)

Which will give output like this:

    date    
------------
 2019-09-29
 2019-09-30
 2019-10-01
 2019-10-02
 2019-10-03
 2019-10-04
 2019-10-05
 2019-10-06
 2019-10-07
 2019-10-08
(10 rows)

Then you can do a query like this (for example), joining the original query as a subquery against whatever table you're ultimately interested in:

   SELECT sequential_dates.date,
          COUNT(calendar_items.*) AS calendar_item_count
     FROM (SELECT CURRENT_DATE + sequential_dates.date AS date
             FROM generate_series(0, 9) AS sequential_dates(date)) sequential_dates
LEFT JOIN calendar_items ON calendar_items.starts_at::date = sequential_dates.date
 GROUP BY sequential_dates.date

Which will give output like this:

    date    | calendar_item_count 
------------+---------------------
 2019-09-29 |                   1
 2019-09-30 |                   8
 2019-10-01 |                  15
 2019-10-02 |                  11
 2019-10-03 |                   1
 2019-10-04 |                  12
 2019-10-05 |                   0
 2019-10-06 |                   0
 2019-10-07 |                  27
 2019-10-08 |                  24

ReferenceURL : https://stackoverflow.com/questions/15691127/postgresql-query-to-count-group-by-day-and-display-days-with-no-data