使用交叉表生成图表数据

Question

我正在尝试进行一个高效的查询，以创建一个视图，该视图将包含按天以及按用户类型的成功登录次数计数，每天没有重复用户。

我有3个表参与了这个查询。一个表包含所有成功登录尝试，一个表用于标准用户帐户，一个表格用于管理用户帐户。所有user_id值在整个数据库中都是唯一的，因此没有用户帐户与管理员帐户共享相同的user_id：

TABLE 1: user_account
user_id | username 
---------|----------
1       | user1
2       | user2
TABLE 2: admin_account
user_id | username 
---------|----------
6       | admin6
7       | admin7
TABLE 3: successful_logins
user_id | timestamp 
---------|------------------------------
1       | 2022-01-23 14:39:12.63798-07
1       | 2022-01-28 11:16:45.63798-07
1       | 2022-01-28 01:53:51.63798-07
2       | 2022-01-28 15:19:21.63798-07
6       | 2022-01-28 09:42:36.63798-07
2       | 2022-01-23 03:46:21.63798-07
7       | 2022-01-28 19:52:16.63798-07
2       | 2022-01-29 23:12:41.63798-07
2       | 2022-01-29 18:50:10.63798-07

我想要生成的结果视图将包含来自以上3个表的以下信息：

VEIW: login_counts
date_of_login | successful_user_logins | successful_admin_logins
---------------|------------------------|-------------------------
2022-01-23    | 1                      | 1
2022-01-28    | 2                      | 2
2022-01-29    | 1                      | 0

我目前正在阅读交叉表是如何工作的，但很难弄清楚如何根据我的表设置编写查询。

实际上，我可以通过使用以下查询来获得所需的值：

SELECT 
to_char(s.timestamp, 'YYYY-MM-DD') AS login_date,
count(distinct u.user_id) AS successful_user_logins,
count(distinct a.user_id) AS successful_admin_logins
FROM successful_logins s
LEFT JOIN user_account u ON u.user_id= s.user_id
LEFT JOIN admin_account a ON a.user_id= s.user_id
GROUP BY login_date

然而，有人告诉我，使用交叉表会更快，尤其是考虑到successful_logins表包含数百万条记录。因此，我还尝试使用交叉表创建一个查询版本，然后比较两个执行时间。

如有任何帮助，我们将不胜感激。谢谢

Answer 1

事实证明，使用交叉表是不可能做到我所要求的，所以我必须执行原始查询。