如何选择多列、求和一列、按多列分组

Question

我有一个名为example的表，其中包含列user_id、date_start和activity

我需要选择user_id，date_start列，计算唯一的user_id，然后按user_id和date_start分组。

表数据：

  ----------------------------------
 | user_id | date_start | activity  |
 |---------|------------|-----------|
 |  1      |2021-04-01  | CATIA     |
 |  1      |2021-04-05  | CATIA     |
 |  1      |2021-04-02  | CATIA     |
 |  1      |2021-05-01  | CATIA     |
 |  1      |2021-05-02  | CATIA     |
 |  3      |2021-05-02  | CATIA     |
 |  3      |2021-05-03  | CATIA     |
 |  4      |2021-05-05  | CATIA     |
  ----------------------------------

此查询：

SELECT FORMAT(d.date_start, 'yyyy-MM'), d.user_id
from (select d.user_id,  date_start,
         count(*) over (partition by user_id) as cnt,
         row_number() over (partition by FORMAT(date_start, 'yyyy-MM') order by FORMAT(date_start, 'yyyy-MM') desc) as seqnum
  from planner d
 ) d
where seqnum = 1;

我需要像这样展示我的代码：

  ---------------------
 | date_start | total  |
 |------------|--------|
 | 2021-04    | 1      |
 | 2021-05    | 3      |
  ---------------------

Answer 1

你在找这个吗？：

select FORMAT(d.date_start, 'yyyy-MM') date_start
     , count(distinct user_id) total
from planner d
group by FORMAT(date_start, 'yyyy-MM')