指定周任意起始日

select arrayJoin([0,1,2,3,4,5,6]) as start_day, 
  toDateTime('2025-04-10') as t,
  toDayOfWeek(t),
  if(start_day <= toDayOfWeek(t), 
     date_add(DAY, -((toDayOfWeek(t) - start_day) % 7), t),
     date_add(DAY, -7-((toDayOfWeek(t) - start_day) % 7), t)
  ) AS week_start_day;

时间戳转时间

select `#time`, timestamp_add(fromUnixTimestamp64Milli(`#time`), interval coalesce(`#zone_offset`, 8) * 60 minute) from events where `#event` = '#device_login' and `#dt` = '2025-03-01' limit 100;

变更语句是否执行完

select hostname(),* from clusterAllReplicas('default','system.mutations') where is_done = 0;

清理数据库表

truncate table demo.events_local;
truncate table demo_global.users_local;
truncate table demo_global.devices_local;

根据 query 查询 clickhouse 日志

select * from cluster('all-sharded', 'system', 'query_log') where query_id  = '4efbbb9b-b853-44ca-84b2-95ff066895af';

数据导出成CSV

SELECT * FROM events
SETTINGS join_use_nulls=1, allow_experimental_analyzer=0
INTO OUTFILE '新玩家首日首场战场数据.csv.gz' TRUNCATE
FORMAT CSVWithNames;

数据去重验证

select `_part`, `#dt`, `#event`, `#log_id`, count(1), groupArray(hostname()) from events where `#dt` = '2024-07-28' group by 1, 2, 3, 4 order by 5 desc limit 100;