PostgreSQL:按时间间隔计数记录 [英] Postgresql: count records by time intervals

问题描述

我正在尝试将以下SQL语句与postgresql一起使用以生成一些报告:

I am trying to use the following SQL statement with postgresql to generate some report:

WITH GRID AS (
  SELECT START_TIME, LEAD(START_TIME) OVER (ORDER BY START_TIME) AS END_TIME 
  FROM  (
    SELECT GENERATE_SERIES('2015-08-01 12:00:00', '2015-09-01 12:00:00',  INTERVAL '1 day') AS START_TIME 
    FROM   MY_TABLE
    ) x
  )
  SELECT 
    START_TIME, COUNT(MY_TABLE._ID) AS COUNT 
    FROM
      GRID 
      LEFT   JOIN MY_TABLE ON MY_TABLE.CREATED >= GRID.START_TIME AND MY_TABLE.CREATED <  GRID.END_TIME  
    WHERE MY_TABLE.COMPANY_ID = '1001'  AND MY_TABLE.CUSTOMER_ID = '1003' 
    GROUP  BY 1 ORDER  BY 1

我期望的是:

• "2015-08-01 12:00:00 + 02"; 0
• "2015-08-02 12:00:00 + 02"; 1
• "2015-08-03 12:00:00 + 02"; 0
• "2015-08-04 12:00:00 + 02"; 1
• "2015-08-05 12:00:00 + 02"; 0
• ....
• "2015-08-31 12:00:00 + 02"; 0

但实际结果是:

• "2015-08-02 12:00:00 + 02"; 1
• "2015-08-04 12:00:00 + 02"; 1

它只是跳过计数为0的记录.

it simply skips the records with 0 count.

如果我不指定where子句(条件部分)，则可以获得预期的结果.

if I don't specify the where clause (condition part), I could get the expected result.

有人可以帮我吗?

谢谢！

推荐答案

以下查询似乎可以满足您的要求:

Folllowing query seems do what you want:

WITH GRID AS
(
  SELECT 
    START_TIME,
    LEAD(START_TIME) OVER (ORDER BY START_TIME) AS END_TIME
  FROM
  (
    SELECT 
      GENERATE_SERIES('2015-08-01 12:00:00', '2015-09-01 12:00:00', INTERVAL '1 day') AS START_TIME
    FROM MY_TABLE 
  ) x 
)
SELECT 
  START_TIME,
  COUNT(MY_TABLE._ID) AS COUNT
FROM GRID
LEFT JOIN MY_TABLE ON 
  MY_TABLE.CREATED >= GRID.START_TIME AND 
  MY_TABLE.CREATED < GRID.END_TIME AND 
  MY_TABLE.COMPANY_ID = '1001' AND 
  MY_TABLE.CUSTOMER_ID = '1003'
GROUP BY 1
ORDER BY 1

这篇关于PostgreSQL:按时间间隔计数记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！