如何获得INNER联接数和所有项目数的计数？

问题描述

我有两个表，paid_users和paid_users_no。基本上，付费用户可以拥有许多帐户。

I have two tables, paid_users and paid_users_no more. Essentially, a paid user can have many accounts.

他们的模式如下：

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


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

我写了一个查询来查询在给定月份哪些账户用来付款，哪些账户不再使用（搅动的帐户）：

I wrote a query to grab which accounts used to pay and which ones no longer do in a given month (churned accounts):

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;

这给了我八月份每个payor_id的搅拌帐户数-我也将如何获取总数付款人拥有多少个帐户？即，以下查询为我提供了每个payor_id的帐户数量：

This gives me the number of churned accounts for each payor_id in August - how would I also grab the total number of accounts the payor had? Ie, the below query gives me the number of accounts for each 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;

我希望能够以某种方式加入两个结果表，以同时看到搅拌帐户和总计帐户（因为我想计算搅动的收入，而某种方式拥有的帐户越多，它们的成本就越便宜）-有没有办法加入这些表格？

I want be able to somehow join the two resulting tables to see both "churned accounts" and "total accounts" (as I want to calculate churned revenue and the more accounts somehow has, the cheaper their cost is) - is there a way to join these tables?

谢谢！

推荐答案

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

If you make your two queries Union Compatible, then you can combine the results and aggregate with an additional query:

    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作为 total_accounts 和 0 AS churned_accounts 表示两个查询具有相同的字段，这使得 UNION 成为可能。

Having the 0 AS "total_accounts" and 0 AS "churned_accounts" means the two queries have the same fields present, which make the UNION possible.

这篇关于如何获得INNER联接数和所有项目数的计数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！