sql - 根据在一段时间内每天计数大于 1 的关联的行数生成直方图

Question

抱歉标题令人困惑，但我不太清楚如何表达我试图生成的查询，我不知道该怎么做。假设我有一个包含、和websites列的表。我有另一个表 ,有列, , 。iddomaincreated_atvisitorsidwebsite_idcreated_at

我要确定的是在一个多月前创建并且在过去一个月中至少有一个访问者的网站中，我如何按上个月拥有一个访问者的天数对网站进行分组或更多访客？

我实际上是在尝试生成一个直方图，其中 x 轴 # 过去一个月中至少有一个访问者的天数（所以它将去 1-30），以及表中网站的 y 轴 #至少有一位访客的天数。

这是一些我想要收集的示例数据：

网站

╔════╤═══════════════════╤════════════╗
║ id │ domain            │ created_at ║
╠════╪═══════════════════╪════════════╣
║ 1  │ example.com       │ 9-28-2018  ║
╟────┼───────────────────┼────────────╢
║ 2  │ google.com        │ 1-1-2018   ║
╟────┼───────────────────┼────────────╢
║ 3  │ test.com          │ 2-1-2018   ║
╟────┼───────────────────┼────────────╢
║ 4  │ amazon.com        │ 3-1-2018   ║
╟────┼───────────────────┼────────────╢
║ 5  │ stackoverflow.com │ 1-2-2013   ║
╚════╧═══════════════════╧════════════╝

访客

╔════╤════════════╤════════════════════════════╗
║ id │ website_id │ created_at                 ║
╠════╪════════════╪════════════════════════════╣
║ 1  │ 1          │ 2018-09-15 01:34:07.917448 ║
╟────┼────────────┼────────────────────────────╢
║ 2  │ 2          │ 2018-09-28T17:51:35+00:00  ║
╟────┼────────────┼────────────────────────────╢
║ 3  │ 2          │ 2018-09-28T17:50:35+00:00  ║
╟────┼────────────┼────────────────────────────╢
║ 4  │ 2          │ 2018-09-28T17:51:21+00:00  ║
╟────┼────────────┼────────────────────────────╢
║ 5  │ 2          │ 2017-11-15 01:34:07.917448 ║
╟────┼────────────┼────────────────────────────╢
║ 6  │ 3          │ 2018-09-14T17:50:08+50:00  ║
╟────┼────────────┼────────────────────────────╢
║ 7  │ 4          │ 2018-09-15T16:50:01+00:00  ║
╟────┼────────────┼────────────────────────────╢
║ 8  │ 4          │ 2018-09-15T16:50:09+00:00  ║
╟────┼────────────┼────────────────────────────╢
║ 9  │ 4          │ 2018-09-16T16:50:10+32:00  ║
╟────┼────────────┼────────────────────────────╢
║ 10 │ 4          │ 2018-09-04T09:50:09+00:00  ║
╟────┼────────────┼────────────────────────────╢
║ 11 │ 5          │ 2017-11-15 01:34:07.917448 ║
╟────┼────────────┼────────────────────────────╢
║ 12 │ 5          │ 2018-09-06T09:50:09+00:00  ║
╟────┼────────────┼────────────────────────────╢
║ 13 │ 5          │ 2018-09-07T09:50:07+12:00  ║
╟────┼────────────┼────────────────────────────╢
║ 14 │ 5          │ 2018-09-08T09:50:02+40:00  ║
╟────┼────────────┼────────────────────────────╢
║ 15 │ 5          │ 2018-09-09T09:50:19+23:00  ║
╟────┼────────────┼────────────────────────────╢
║ 16 │ 5          │ 2018-09-10T09:50:12+00:00  ║
╟────┼────────────┼────────────────────────────╢
║ 17 │ 5          │ 2018-09-10T09:50:06+00:00  ║
╟────┼────────────┼────────────────────────────╢
║ 18 │ 6          │ 2018-09-02T09:50:01+18:00  ║
╟────┼────────────┼────────────────────────────╢
║ 19 │ 6          │ 2018-09-03T09:50:02+47:00  ║
╟────┼────────────┼────────────────────────────╢
║ 20 │ 6          │ 2018-09-04T09:50:07+13:00  ║
╚════╧════════════╧════════════════════════════╝

输出

╔═════════════════════════════════════╤════════════════════╗
║ Number of Active Days in Last Month │ Number of Websites ║
╠═════════════════════════════════════╪════════════════════╣
║ 0                                   │ 0                  ║
╟─────────────────────────────────────┼────────────────────╢
║ 1                                   │ 2                  ║
╟─────────────────────────────────────┼────────────────────╢
║ 2                                   │ 0                  ║
╟─────────────────────────────────────┼────────────────────╢
║ 3                                   │ 2                  ║
╟─────────────────────────────────────┼────────────────────╢
║ 4                                   │ 0                  ║
╟─────────────────────────────────────┼────────────────────╢
║ 5                                   │ 1                  ║
╟─────────────────────────────────────┼────────────────────╢
║ 6                                   │ 0                  ║
╚═════════════════════════════════════╧════════════════════╝

解释

• 上个月没有一个网站没有访问者。
• 网站 2 和 3 在上个月各有一个活跃日。网站 1 也是如此，但它被排除在外，因为它也是在上个月创建的。
• 上个月没有网站有两天活跃。
• 网站 4 和 6 在上个月各有 3 个活跃日。
• 网站 5 有 5 个活跃日。
• 上个月没有网站有 6 天以上的活跃天数。

标签： sqlpostgresql