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

使用Postgres中每个客户的记录计算天数

distinct postgresql

Milkncookiez · 技术社区 · 7 年前

假设我有以下数据(注意id到日期的差异):

customer_id  created_at
1000         2017-12-29 20:48:54+00
1000         2017-12-30 12:48:56+00
1000         2017-12-30 12:49:26+00
1002         2017-12-30 12:52:36+00
1001         2017-12-30 12:54:15+00
1002         2017-12-30 13:54:15+00
1001         2017-12-30 13:56:58+00
1000         2018-01-02 13:01:13+00
1001         2018-01-02 20:29:19+00
1002         2018-01-02 20:29:31+00
1000         2018-01-03 20:30:28+00
1001         2018-01-03 20:38:40+00

我想获得客户记录的天数。如果一个客户在一天内做了多个记录,那么它仍然计为1。因此,上述数据的输出应为:

customer_id  count
1000         4
1001         3
1002         2

我尝试了不同的查询,试图利用 to_char(created_at, 'YYYY-mm-dd') 和 DISTINCT ON(created_at) 和 count ,但我没有得到我想要的汇总结果。F、 e.:

SELECT distinct on (to_char(created_at, 'YYYY-mm-dd')) count(customer_id), customer_id
FROM registration 
WHERE created_at >= '2017-12-29' and created_at <= '2018-01-03' and customer_id in (1000,1001,1002)
group by customer_id, created_at;

2 回复 | 直到 7 年前

klin 7 年前

使用 distinct 在派生表(中的子查询 from 条款):

select customer_id, count(created_at)
from (
    select distinct customer_id, created_at::date
    from registration
    ) s
group by 1
order by 1;

 customer_id | count 
-------------+-------
        1000 |     4
        1001 |     3
        1002 |     2
(3 rows)

实际上,用户1001活动了3天,而不是4天。

Michel Milezzi 7 年前

你可以使用 DISTINCT 在…内 COUNT :

WITH sample (customer_id, created_at) AS (
    VALUES
        (1000, '2017-12-29 20:48:54+00'::TIMESTAMP),
        (1000, '2017-12-30 12:48:56+00'),
        (1000, '2017-12-30 12:49:26+00'),
        (1002, '2017-12-30 12:52:36+00'),
        (1001, '2017-12-30 12:54:15+00'),
        (1002, '2017-12-30 13:54:15+00'),
        (1001, '2017-12-30 13:56:58+00'),
        (1000, '2018-01-02 13:01:13+00'),
        (1001, '2018-01-02 20:29:19+00'),
        (1002, '2018-01-02 20:29:31+00'),
        (1000, '2018-01-03 20:30:28+00'),
        (1001, '2018-01-03 20:38:40+00')    
)
SELECT 
    customer_id, 
    COUNT(DISTINCT created_at::DATE) 
FROM 
    sample 
GROUP BY 
    customer_id

推荐文章

Johnny T · 基于当前值的SQL合并表[重复]

6 月前

Kallydi · jdbc无法连接到docker中的postgresql数据库

7 月前

dietzi96 · Pandas DataFrame.to_sql随机和静默地失败,没有错误消息

7 月前

Peter Schofield · 类型转换Postgresql

7 月前

BarÄ±Å UÅaklÄ± · 如何在ON冲突更新中使用CTE中的值

7 月前

Kevin Smeeks · Pyspark JDBC分区读取

7 月前

Micah Armantrout · 如何使用sql脚本将数据插入到代码优先的数据库中?

7 月前

Vanortton · 使用Docker开始应用程序开发还是在开发过程中配置Docker?[关闭]

7 月前

Andrus · 如何在sql中查找第二个匹配项

7 月前

MWood · Postgres jsonpath用于引用单例元素或元素数组

7 月前