Блог

Допустим, у меня есть таблица с user_id Int32 типом и login_time как DateTime в UTC формате.
user_id не является уникальным, поэтому SELECT user_id, login_time FROM some_table; дает следующий результат:

 ┌─user_id─┬──login_time─┐
│    1    │  2021-03-01 │
│    1    │  2021-03-01 │
│    1    │  2021-03-02 │
│    2    │  2021-03-02 │
│    2    │  2021-03-03 │
└─────────┴─────────────┘
 

Если я запущу SELECT COUNT(*) as count, toDate(login_time) as l FROM some_table GROUP BY l , я получу следующий результат:

 ┌─count───┬──login_time─┐
│    2    │  2021-03-01 │
│    2    │  2021-03-02 │
│    1    │  2021-03-03 │
└─────────┴─────────────┘
 

Я хотел бы переформатировать результат, чтобы показывать COUNT его на еженедельном уровне, а не каждый день, как я делаю в настоящее время.
Мой результат для приведенного выше примера может выглядеть примерно так:

 ┌──count──┬──year─┬──month──┬─week ordinal┐
│    5    │  2021 │    03   │       1     │
│    0    │  2021 │    03   │       2     │
│    0    │  2021 │    03   │       3     │
│    0    │  2021 │    03   │       4     │
└─────────┴───────┴─────────┴─────────────┘
 

Я просмотрел документацию, нашел несколько интересных функций, но мне не удалось заставить их решить мою проблему.
Я никогда clickhouse раньше не работал с SQL и не очень разбираюсь в нем, поэтому я обращаюсь сюда за помощью.

Попробуйте выполнить этот запрос:

 select count() count, toYear(start_of_month) year, toMonth(start_of_month) month,
       toWeek(start_of_week) - toWeek(start_of_month)   1 AS "week ordinal"
from (
    select *, toStartOfMonth(login_time) start_of_month,
         toStartOfWeek(login_time) start_of_week
    from (
      /* emulate test dataset */
      select data.1 user_id, toDate(data.2) login_time
      from (
        select arrayJoin([
          (1, '2021-02-27'),            
          (1, '2021-02-28'),      
          (1, '2021-03-01'),
          (1, '2021-03-01'),
          (1, '2021-03-02'),
          (2, '2021-03-02'),
          (2, '2021-03-03'),
          (2, '2021-03-08'),
          (2, '2021-03-16'),
          (2, '2021-04-01')]) data)
      )
  )
group by start_of_month, start_of_week
order by start_of_month, start_of_week

/*
┌─count─┬─year─┬─month─┬─week ordinal─┐
│     1 │ 2021 │     2 │            4 │
│     1 │ 2021 │     2 │            5 │
│     5 │ 2021 │     3 │            1 │
│     1 │ 2021 │     3 │            2 │
│     1 │ 2021 │     3 │            3 │
│     1 │ 2021 │     4 │            1 │
└───────┴──────┴───────┴──────────────┘
*/