gpt4 book ai didi

mysql - 在sql中组合多个计数查询

转载 作者:可可西里 更新时间:2023-11-01 07:58:05 26 4
gpt4 key购买 nike

我有一个包含 3 列的用户表,我正在尝试对其进行计数,然后按创建日期分组。

列:'created_at'、'answers_count'、'questions_count'

我如何将这 3 个查询合并为一个查询并将所有计数按相同的 created_at 日期分组?

这是 3 个独立的查询:

-- daily new signups
SELECT date_trunc('day', users.created_at) AS signup_date, count(*) AS new_users
FROM users
GROUP BY signup_date
ORDER BY signup_date DESC;

-- new user answers_count by signup date
SELECT date_trunc('day', users.created_at) AS signup_date, count(*) AS answers_count
FROM users
WHERE users.answers_count > 0
GROUP BY signup_date
ORDER BY signup_date DESC;

-- new user questions_count by signup date
SELECT date_trunc('day', users.created_at) AS signup_date, count(*) AS qs_received
FROM users
WHERE users.questions_count > 0
GROUP BY signup_date
ORDER BY signup_date DESC;

最佳答案

您应该尝试使用 SUM()CASE 来完成此操作。

尝试这样的事情:

SELECT date_trunc('day', users.created_at) AS signup_date, 
count(*) AS new_users,
sum(case when answers_count > 0 then 1 else 0 end) as answers_count,
sum(case when questions_count > 0 then 1 else 0 end) as qs_received,
FROM users
GROUP BY signup_date
ORDER BY signup_date DESC;

关于mysql - 在sql中组合多个计数查询,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/42400730/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com