Clickhouse的 MySQL ELT和interval 功能等同实现 roundDown

最新推荐文章于 2023-10-26 19:02:29 发布

vkingnew

最新推荐文章于 2023-10-26 19:02:29 发布

阅读量1.1k

点赞数 1

分类专栏： Clickhouse

本文链接：https://blog.csdn.net/vkingnew/article/details/108347635

版权

Clickhouse 专栏收录该内容

102 篇文章 70 订阅

订阅专栏

在MySQL和Clickhouse中分别创建表：

mysql> create table scores(id int not null auto_increment primary key,user_id bigint,scores decimal(10,2));

Clickhouse> create table scores(user_id bigint,scores decimal(10,2)) engine=Memory;


insert into scores(user_id,scores)values(101,60.5),(102,50.25),(103,80),(104,90),(105,99),(106,92.75)
,(107,75),(108,89.75),(109,90.25),(110,0),(111,60),(112,100);



mysql> select user_id,scores,interval(scores,0,60,80,90) p from scores;
+---------+--------+---+
| user_id | scores | p |
+---------+--------+---+
|     101 |  60.50 | 2 |
|     102 |  50.25 | 1 |
|     103 |  80.00 | 3 |
|     104 |  90.00 | 4 |
|     105 |  99.00 | 4 |
|     106 |  92.75 | 4 |
|     107 |  75.00 | 2 |
|     108 |  89.75 | 3 |
|     109 |  90.25 | 4 |
|     110 |   0.00 | 1 |
|     111 |  60.00 | 2 |
|     112 | 100.00 | 4 |
+---------+--------+---+
12 rows in set (0.00 sec)

适用于多数RDBMS数据库：

select case when scores>=0 and scores <60 then '[0,60)' 
             when scores>=60 and scores <80 then '[60,80)'  
			 when scores>=80 and scores <90 then '[80,90)' 
			 when scores>=90 and scores <=100 then '[90,100]' 
			 end p
			 ,count(user_id) 
from scores group by p order by 1;

+----------+----------------+
| p        | count(user_id) |
+----------+----------------+
| [0,60)   |              2 |
| [60,80)  |              3 |
| [80,90)  |              2 |
| [90,100] |              5 |
+----------+----------------+
4 rows in set (0.00 sec)

MySQL中的ELT和Interval 配合使用：


mysql> set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)


mysql> select elt(interval(scores,0,60,80,90),'[0,60)','[60-80)','[80,90)','[90,100]') p,count(user_id) 
from scores 
group by elt(interval(scores,0,60,80,90),'[0,60)','[60-80)','[80,90)','[90,100]') order by 1 ;
+----------+----------------+
| p        | count(user_id) |
+----------+----------------+
| [0,60)   |              2 |
| [60-80)  |              3 |
| [80,90)  |              2 |
| [90,100] |              5 |
+----------+----------------+
4 rows in set (0.00 sec)


MySQL中 也支持列的别名
mysql> select elt(interval(scores,0,60,80,90),'[0,60)','[60-80)','[80,90)','[90,100]') p,count(user_id) 
 from scores  group by p order by 1;

Clickhouse的multiif函数：

Clickhouse> select case when scores>=0 and scores <60 then '[0,60)' when scores>=60 and scores <80 then '[60,80)'  when scores>=80 and scores <90 then '[80,90)' when scores>=90 and scores <=100 then '[90,100]' end p,count(user_id) from scores group by p order by 1;

SELECT 
    multiIf((scores >= 0) AND (scores < 60), '[0,60)', (scores >= 60) AND (scores < 80), '[60,80)', (scores >= 80) AND (scores < 90), '[80,90)', (scores >= 90) AND (scores <= 100), '[90,100]', NULL) AS p,
    count(user_id)
FROM scores
GROUP BY p
ORDER BY 1 ASC

┌─p────────┬─count(user_id)─┐
│ [0,60)   │              2 │
│ [60,80)  │              3 │
│ [90,100] │              5 │
│ [80,90)  │              2 │
└──────────┴────────────────┘

4 rows in set. Elapsed: 0.005 sec.

Clickhouse中提供了类似于MySQL的ELT的函数roundDown

SELECT
    roundDown(scores, [0, 60, 80, 90]) AS p,
    count(user_id)
FROM scores
GROUP BY p
ORDER BY p ASC

┌─────p─┬─count(user_id)─┐
│  0.00 │              2 │
│ 60.00 │              3 │
│ 80.00 │              2 │
│ 90.00 │              5 │
└───────┴────────────────┘


--- 可以细分为

SELECT
    user_id,
    scores,
    roundDown(scores, [0, 60, 80, 90])
FROM scores

┌─user_id─┬─scores─┬─roundDown(scores, [0, 60, 80, 90])─┐
│     101 │  60.50 │                              60.00 │
│     102 │  50.25 │                               0.00 │
│     103 │  80.00 │                              80.00 │
│     104 │  90.00 │                              90.00 │
│     105 │  99.00 │                              90.00 │
│     106 │  92.75 │                              90.00 │
│     107 │  75.00 │                              60.00 │
│     108 │  89.75 │                              80.00 │
│     109 │  90.25 │                              90.00 │
│     110 │   0.00 │                               0.00 │
│     111 │  60.00 │                              60.00 │
│     112 │ 100.00 │                              90.00 │
└─────────┴────────┴────────────────────────────────────┘

12 rows in set. Elapsed: 0.004 sec.

可以看到roundDown函数和MySQL的ELT函数功能一致。

参考：

https://github.com/ClickHouse/ClickHouse/blob/master/tests/queries/0_stateless/00805_round_down.sql