postgresql - 如何为在任何平台上连接的用户按月/年进行计数？

Question

我有一个名为 users 的表，其中包含用户 ID，还有一些表，例如 cloud_storage_a、cloud_storage_b 和 cloud_storage_c。如果用户存在于 cloud_storage_a 中，则表示他们已连接到云存储 a。一个用户也可以存在于多个云存储中。这是一个例子:

id      | address      | name      | created_at
--------------------------------------------------
123     | 23 Oak Ave   | Melissa   | 2014-05-12
333     | 18 Robson Rd | Steve     | 2015-01-20
421     | 95 Ottawa St | Helen     | 2015-02-10
555     | 12 Highland  | Amit      | 2015-05-17
192     | 39 Anchor Rd | Oliver    | 2015-08-25

cloud_storage_a

user_id | created_at
---------------------
 421    | 2015-03-05
 333    | 2015-02-01

cloud_storage_b

user_id | created_at
----------------------
 555    | 2015-07-20

cloud_storage_c

user_id | created_at
---------------------
 192    | 2015-08-26
 555    | 2015-08-01

本例中，用户555于05-2015注册，但直到07-2015才添加云存储b，并于08-2015加入云存储c。我目前有一个查询，以确定用户连接到按月/年分组的每个云存储所需的平均天数:

SELECT CONCAT(t1."Month",'-',t1."Year") as "Month-Year", a_connection_date::int AS "Average Days to Connect to A", 
b_connection_date::int as "Average Days to Connect to B", c_connection_date::int as "Average Days to Connect to C"
from ( 
  SELECT extract(YEAR from u.created_at) as "Year", extract(MONTH from u.created_at) as "Month", 
  AVG(extract (day from c.created_at - u.created_at)) as a_connection_date
  FROM users u INNER JOIN cloud_storage_a c ON u.id=c.user_id
  GROUP BY 1, EXTRACT(MONTH from u.created_at), EXTRACT(YEAR from u.created_at)
  order by "Year","Month") as t1
full join (SELECT extract(YEAR from u.created_at) as "Year", 
  extract(MONTH from u.created_at) as "Month", AVG(extract (day from 
  c.created_at - u.created_at)) as b_connection_date
  FROM users u INNER JOIN cloud_storage_b c ON u.id=c.user_id
  GROUP BY 1, EXTRACT(MONTH from u.created_at), EXTRACT(YEAR from u.created_at)
  order by "Year","Month"
) as t2
  on concat(t1."Year",'-',t1."Month")=concat(t2."Year",'-',t2."Month")
  full join (SELECT extract(YEAR from u.created_at) as "Year",     
  extract(MONTH from u.created_at) as "Month", AVG(extract (day from      
  c.created_at - u.created_at)) as c_connection_date
  FROM users u INNER JOIN cloud_storage_c c ON u.id=c.user_id
  GROUP BY 1, EXTRACT(MONTH from u.created_at), EXTRACT(YEAR from   u.created_at)
  order by "Year","Month"
) as t3
on concat(t1."Year",'-',t1."Month")=concat(t3."Year",'-',t3."Month")
ORDER BY t1."Year",t1."Month";

返回的内容如下:

Month-Year | Average Days to Connect to A | Average Days to Connect to B | Average Days to Connect to C 
---------------------------------------------------------------------------------------------------------
1-2015     | 11                           |                              |
2-2015     | 23                           |                              |                              
5-2015     |                              | 63                           | 73
8-2015     |                              |                              | 1

我还想为“连接到任何云的平均天数”设置一列(即对于用户 555，他们在云 c 之前连接到云 b，因此 5-2015 的平均天数将改为 63 天共 73 个。请注意，此示例是一个小子集，因此可能有许多用户在 5-2015 年注册并连接到各种云!

Answer 1

创建一个 View 来保存所有云存储:

create view all_cloud_storages as
select user_id, created_at from cloud_storage_a union
select user_id, created_at from cloud_storage_b union
select user_id, created_at from cloud_storage_c union
....
select user_id, created_at from cloud_storage_z;

然后更改您编写的“选择”以从新的“ View ”中检索记录:

SELECT CONCAT(t1."Month",'-',t1."Year") as "Month-Year", 
       connection_date::int AS "Average Days to Connect to Any",
       first_connection_date as "Date of First Connection in Month",
       last_connection_date as "Date of Last Connection in Month"
FROM   ( 
         SELECT  extract(YEAR from u.created_at) as "Year", 
                 extract(MONTH from u.created_at) as "Month", 
                 AVG(extract (day from c.created_at - u.created_at)) 
                    as connection_date,
                 MIN(c.created_at) AS first_connection_date,
                 MAX(c.created_at) AS last_connection_date
           FROM  users u 
           INNER JOIN all_cloud_storages c ON u.id=c.user_id
           GROUP BY 1, 
                    EXTRACT(MONTH from u.created_at), 
                    EXTRACT(YEAR from u.created_at)
           order by "Year","Month"
       ) as t1
 ORDER BY t1."Year",t1."Month";