gpt4 book ai didi

postgresql - 如何获取 INNER join 的计数和所有项目的计数?

转载 作者:行者123 更新时间:2023-11-29 13:26:34 24 4
gpt4 key购买 nike

我有两个表,paid_users 和 paid_users_no more。从本质上讲,付费用户可以拥有多个帐户。

他们的模式看起来像这样:

paid_users:
payor_id | user_email | payor_email | payment_start_date
---------------------------------------------------------
| | |


paid_users_no_more:
user_id | payment_stop_date
---------------------------
|

我写了一个查询来获取在给定月份(流失的账户)中哪些账户用于支付以及哪些账户不再支付:

SELECT payor_id, count(*) as "churned accounts" FROM paid_users_no_more 
INNER JOIN paid_users
ON paid_users_no_more.user_id=paid_users.user_id
WHERE paid_users.payment_start_date NOT BETWEEN '2015-08-01 00:00:00'::timestamp AND '2015-08-30 23:59:59'::timestamp
AND paid_users_no_more.payment_stop_date BETWEEN '2015-08-01 00:00:00'::timestamp AND '2015-08-30 23:59:59'::timestamp
GROUP BY paid_users.payor_id;

这为我提供了 8 月份每个 payor_id 的流失账户数量 - 我如何获取付款人拥有的账户总数?即,以下查询为我提供了每个 payor_id 的帐户数量:

SELECT paid_users.payor_email,count(*) AS "total accounts" 
FROM paid_users
WHERE paid_users.payment_start_date NOT BETWEEN '2015-08-01 00:00:00'::timestamp AND '2015-08-30 23:59:59'::timestamp
GROUP BY paid_users.payor_email;

我希望能够以某种方式连接两个结果表以查看“流失账户”和“总账户”(因为我想计算流失收入,并且不知何故拥有的账户越多,它们的成本越低)- 有吗加入这些表的方法?

谢谢!

最佳答案

如果您使两个查询成为 Union Compatible,那么您可以将结果合并并与其他查询聚合:

    SELECT payer, 
sum(churned_accounts) AS "churned_count",
sum(total_accounts) AS "total_count"
FROM (
SELECT CAST(payor_id AS CHAR(50)) AS "payer",
count(*) as "churned accounts",
0 AS "total accounts"
FROM paid_users_no_more
INNER JOIN paid_users
ON paid_users_no_more.user_id=paid_users.user_id
WHERE paid_users.payment_start_date NOT BETWEEN '2015-08-01 00:00:00'::timestamp AND '2015-08-30 23:59:59'::timestamp
AND paid_users_no_more.payment_stop_date BETWEEN '2015-08-01 00:00:00'::timestamp AND '2015-08-30 23:59:59'::timestamp
GROUP BY paid_users.payor_id

UNION

SELECT CAST(paid_users.payor_email AS CHAR(50)) AS "payer",
0 AS "churned accounts",
count(*) AS "total accounts"
FROM paid_users
WHERE paid_users.payment_start_date NOT BETWEEN '2015-08-01 00:00:00'::timestamp AND '2015-08-30 23:59:59'::timestamp
GROUP BY paid_users.payor_email
) as All_Accounts

0 AS "total_accounts"0 AS "churned_accounts" 意味着两个查询具有相同的字段,这使得 UNION 可能。

关于postgresql - 如何获取 INNER join 的计数和所有项目的计数?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/32832243/

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