代码之家 › 专栏 › 技术社区 › Mateusz UrbaÅski

每天创建的记录数

postgresql sql

Mateusz UrbaÅski · 技术社区 · 7 年前

在我的PostgreSQL数据库中,我有以下模式:

CREATE TABLE programs (
    id integer,
    description text
);

CREATE TABLE public.messages (
    id integer,
    program_id integer,
    text text,
    message_template_id integer
);

CREATE TABLE public.message_templates (
    id integer,
    deliver_day integer
);

INSERT INTO programs VALUES(1, 'Test program');

INSERT INTO messages VALUES(1,1, 'Test message 1', 1);
INSERT INTO message_templates VALUES(1, 1);

INSERT INTO messages VALUES(2,1, 'Test message 2', 2);
INSERT INTO message_templates VALUES(2, 3);

INSERT INTO messages VALUES(3,1, 'Test message 3', 3);
INSERT INTO message_templates VALUES(3, 5);

现在,我想获得程序生命周期内每天发送的消息数,查询结果如下:

  day      count
--------|----------
   1         1
   2         0
   3         1
   4         0
   5         1

在PostgreSQL中有没有这样做的方法?

https://www.db-fiddle.com/f/gvxijmp8u6wr6mYcSoAeVV/2

2 回复 | 直到 7 年前

Mateusz UrbaÅski 7 年前

我决定用 generate_series :

SELECT d AS "Day", count(mt.id)  FROM generate_series(
  (SELECT min(delivery_day) from message_templates),
  (SELECT max(delivery_day) from message_templates)
) d
left join message_templates mt on mt.delivery_day = d 
group by d.d

查询工作正常。也许有更好的方法?

Pham X. Bach 7 年前

WITH tmp AS 
( 
    SELECT m.program_id, a.n AS d
    FROM generate_series(1, 
        (SELECT MAX(deliver_day) FROM message_templates) 
    )  AS a(n)
    CROSS JOIN 
    (
        SELECT DISTINCT program_id
        FROM messages
    ) m
)
SELECT t.program_id,
    t.d AS "day", 
    COUNT(m.program_id) AS "count" -- COUNT(m.id) 
FROM tmp t
LEFT JOIN message_templates mt
ON t.d = mt.deliver_day 
LEFT JOIN messages m
ON m.message_template_id = mt.id AND t.program_id = m.program_id
GROUP BY t.program_id, t.d 
ORDER BY t.program_id, t.d;

测试 db-fiddle