SQL按日期对唯一出现的事件进行分组

Question

我正在寻找一个MySQL查询来统计有多少人访问了一个web应用程序。我有一个数据库，存储访问者的信息，他们键入以及何时尝试登录：

+-----+-----------+--------+---------+---------------------+
| id  | person_id | area   | success | date_created        |
+-----+-----------+--------+---------+---------------------+
| 762 |       100 | client |       1 | 2022-06-28 09:22:35 |
| 763 |       100 | client |       1 | 2022-06-28 11:22:35 |
| 764 |       100 | client |       1 | 2022-06-28 14:22:35 |
| 765 |       102 | client |       1 | 2022-06-28 15:04:43 |
| 766 |       101 |   user |       1 | 2022-06-28 16:04:43 |
| 767 |       100 | client |       1 | 2022-06-29 15:07:29 |
| 768 |       101 |   user |       1 | 2022-06-30 09:18:41 |
| 769 |       100 | client |       1 | 2022-07-01 09:24:46 |
| 770 |       101 |   user |       1 | 2022-07-01 09:25:59 |
| 771 |       102 | client |       1 | 2022-07-01 14:17:57 |
| 772 |       103 | client |       1 | 2022-07-01 15:20:14 |
| 773 |       100 | client |       1 | 2022-07-02 13:01:49 |
| 774 |       100 | client |       1 | 2022-07-03 09:06:00 |
| 775 |       102 | client |       0 | 2022-07-03 12:11:23 |
| 776 |       103 | client |       1 | 2022-07-03 12:41:43 |

我需要解析date_created到date，如果are是client，则对person_id的唯一出现次数求和，并按日期求和。

用其他编程语言做这件事不会有问题，但我很难用SQL构建查询。需要在盒子上做，但不能在那里安装太多。

预期结果：

+------------+--------------------+
| date       | successful_clients |
+------------+--------------------+
| 2022-06-28 |                  2 |
| 2022-06-29 |                  1 |
| 2022-06-30 |                  0 |
| 2022-07-01 |                  3 |
| 2022-07-02 |                  1 |
| 2022-07-03 |                  2 |

什么是高效的SQL查询？

Answer 1

使用条件聚合：

SELECT DATE(date_created) date,
COUNT(DISTINCT CASE WHEN success AND area = 'client' THEN person_id END) successful_clients 
FROM tablename
GROUP BY date
ORDER BY date;

请参阅演示