代码之家 › 专栏 › 技术社区 › Richard

postgres:使用特定于每个组条件的条件运行group by?

postgresql

Richard · 技术社区 · 6 年前

我在开Postgres9.6。我有一个用户表:

 id                            | integer                     |           | not null | nextval('user_id_seq'::regclass)
 name                          | character varying           |           | not null | 
 t_registered       | timestamp without time zone |           | not null |

我有一个 action 表格:

 actioned_by      | integer                     |           |          | 
 category         | character varying           |           | not null | 
 t_actioned       | timestamp without time zone |           | not null |

我想用一个整型字段来注释每个用户 num_days

我知道如何获取每个用户活动的不同日历日总数:

 SELECT d.actioned_by, COUNT(*) AS cnt FROM 
  (SELECT date_trunc('day', t_actioned) AS day, actioned_by 
   FROM history 
   GROUP BY day, actioned_by) d 
 GROUP BY actioned_by
 ORDER BY cnt DESC;

但是我被困在如何在30天的时间窗口,具体到每个用户。

我只是偶尔为了分析的目的运行这个,所以它是否慢并不重要。

2 回复 | 直到 6 年前

404 Aniket Jha 6 年前

SELECT actioned_by, COUNT(*)
FROM (
        SELECT actioned_by, date_trunc('day', t_actioned)
        FROM history
        JOIN user ON id = actioned_by
        WHERE t_actioned BETWEEN t_registered AND t_registered + interval '30 days'
        GROUP BY actioned_by, date_trunc('day', t_actioned)
) sub
GROUP BY actioned_by

所以首先你说你想数一数 不同的 天,因此您必须按日期分组,否则您将计算同一天的多个事件(如果存在)。

这个查询的作用是,加入 user t_actioned 是在注册后30天内,然后按用户和唯一日期分组,最后按用户计数。

如果要包含不执行任何操作的用户,则可以稍微更改:从用户表中选择,然后左键连接历史记录:

SELECT id, COUNT(*)
FROM (
        SELECT id, date_trunc('day', t_actioned)
        FROM user
        LEFT JOIN history
            ON id = actioned_by
            AND t_actioned BETWEEN t_registered AND t_registered + interval '30 days'
        GROUP BY id, date_trunc('day', t_actioned)
) sub
GROUP BY id

Michel Milezzi 6 年前

你需要一个 COUNT 具有 FILTER 表达式:

SELECT 
    u.id,
    u."name",
    COUNT(*) FILTER (WHERE h.t_actioned BETWEEN u.t_registered AND u.t_registered + INTERVAL'30 days')
FROM 
    history h
    JOIN "user" u ON u.id = h.actioned_by
GROUP BY
    u.id,
    u."name"

here .