mysql, presto, clickhouse 常用 sql 差异
介绍
| mysql | presto | clickhouse | postgre sql | 备注 |
|---|---|---|---|---|
| count(DISTINCT sybn) | count(DISTINCT sybn) | count(DISTINCT sybn) // 默认执行 uniqExact 算法 uniq(sybn) // hash 快速去重最不精确 uniqCombined // HLL省内存不太精确 uniqExact // 标准逻辑硬算精确去重 |
count(DISTINCT sybn) | 参考连接 |
| group_concat(DISTINCT code SEPARATOR ',') | array_join(array_agg(DISTINCT code), ',') | arrayStringConcat(groupUniqArray(code), ',') | array_to_string(array_agg(distinct code), ',') | 按特定符号分隔所有字段成字符串 |
| group_concat(code SEPARATOR ',') | array_join(array_agg(code), ',') | arrayStringConcat(groupArray(code), ',') | array_to_string(array_agg(code), ',') | 注意: 多个字段公用 groupArray 时, 需要将groupArray 单独定义为变量 |
| group_concat(DISTINCT code ORDER BY number DESC SEPARATOR ',') | array_distinct(array_agg(code ORDER BY number DESC, 1, 10)) -- 最后10次观影去重 | topK(code) -- 最频繁的10部影片 | ) | 效果类似, 但不完全等效 |
| SELECT flag, code, date_key FROM ( SELECT row_number() OVER (PARTITION BY code ORDER BY update_time DESC) code, date_key, flag FROM table WHERE ... ) -- 获取每个 code 最新的值 |
SELECT code, argMax(flag, update_time) AS flag, argMax(date_key, update_time) AS date_key FROM table WHERE ... GROUP BY code |
) | 完全等效 | |
| cast(id AS varchar) | cast(id AS varchar) | toString(id) | cast(id AS varchar) | 几乎完全等效 |
| date_diff('day', cast('2024-04-03' AS date), cast(now() AS date)) | dateDiff('day', cast('2024-04-03' AS date), today()) | date_part('day', day '2024-04-03' - day '2024-04-01') | ||
| date_add(now(), INTERVAL 1 DAY) | date_add(now(), INTERVAL 1 DAY) | addDays(now(), 1) | now() + interval '1 DAY' | |
| curdate() | today() | ) |
