|NO.Z.00036|——————————|BigDataEnd|——|Hadoop&OLAP_ClickHouse.V08|——|ClickHouse.v08|ClickHouse语法|with子句|

一、with子句

### --- with子句
~~~     本节提供对公共表表达式的支持 (CTE），
~~~     所以结果 WITH 子句可以在其余部分中使用 SELECT 查询。
                        
~~~     # 限制
~~~     不支持递归查询。
~~~     当在section中使用子查询时，它的结果应该是只有一行的标量。
~~~     Expression的结果在子查询中不可用。

二、with子句实验示例：

### --- 示例1: 使用常量表达式作为 “variable”

~~~     # 使用常量表达式作为 “variable”
hadoop01 :) with '2021-11-02 14:20:22' as tm select toDate(tm);
┌─toDate(tm)─┐
│ 2021-11-02 │
└────────────┘

### --- 示例2: 从SELECT子句列表中逐出sum(bytes)表达式结果

~~~     # 从SELECT子句列表中逐出sum(bytes)表达式结果
hadoop01 :) WITH sum(bytes) as s
            SELECT
            formatReadableSize(s),
            table
            FROM system.parts
            GROUP BY table
            ORDER BY s;
~~~ 输出参数
┌─formatReadableSize(s)─┬─table───────────────────┐
│ 697.00 B              │ partition_v1            │
│ 4.32 KiB              │ mt_table                │
│ 8.48 KiB              │ trace_log               │
│ 50.40 KiB             │ asynchronous_metric_log │
│ 118.66 KiB            │ query_thread_log        │
│ 125.69 KiB            │ query_log               │
│ 6.61 MiB              │ metric_log              │
└───────────────────────┴─────────────────────────┘

### --- 例3: 使用标量子查询的结果

~~~     # 使用标量子查询的结果；没有 where acitive条件限制
hadoop01 :) use system;

hadoop01 :) with sum(bytes) as s
            select formatReadableSize(s) from parts;
~~~输出参数
┌─formatReadableSize(s)─┐
│ 6.83 MiB              │
└───────────────────────┘

~~~     # 有where active条件限制

hadoop01 :) with sum(bytes) as s
            select formatReadableSize(s)
            from parts
            where active;
~~~输出参数
┌─formatReadableSize(s)─┐
│ 903.41 KiB            │
└───────────────────────┘

~~~     # 不用with定义变量的写法

hadoop01 :) select formatReadableSize(sum(bytes)) 
            as f, 
            table from parts group by table;
~~~输出参数
┌─f──────────┬─table───────────────────┐
│ 1.80 KiB   │ mt_table                │
│ 6.79 MiB   │ metric_log              │
│ 697.00 B   │ partition_v1            │
│ 9.12 KiB   │ trace_log               │
│ 244.15 KiB │ query_thread_log        │
│ 263.73 KiB │ query_log               │
│ 69.32 KiB  │ asynchronous_metric_log │
└────────────┴─────────────────────────┘

~~~     # 相同结果，用with定义变量

hadoop01 :) with sum(bytes) 
            as a select formatReadableSize(a) as s,
            table from parts group by
            table;
~~~输出参数
┌─s──────────┬─table───────────────────┐
│ 1.80 KiB   │ mt_table                │
│ 6.81 MiB   │ metric_log              │
│ 697.00 B   │ partition_v1            │
│ 9.12 KiB   │ trace_log               │
│ 247.11 KiB │ query_thread_log        │
│ 267.04 KiB │ query_log               │
│ 71.19 KiB  │ asynchronous_metric_log │
└────────────┴─────────────────────────┘

~~~     # /* this example would return TOP 10 of most huge tables */

hadoop01 :) WITH ( SELECT sum(bytes)
            FROM system.parts
            WHERE active ) AS total_disk_usage
            SELECT
            (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,
            table FROM system.parts
            GROUP BY table
            ORDER BY table_disk_usage DESC
            LIMIT 10;
~~~输出参数
┌────table_disk_usage─┬─table───────────────────┐
│   780.7309745639874 │ metric_log              │
│   30.05547128880357 │ query_log               │
│  27.801766959784675 │ query_thread_log        │
│   5.770903251514358 │ asynchronous_metric_log │
│   1.014204949528619 │ trace_log               │
│ 0.19967058154192596 │ mt_table                │
│ 0.07567721334133898 │ partition_v1            │
└─────────────────────┴─────────────────────────┘

### --- 例4: 在子查询中重用表达式

~~~     # 子查询中重用表达式：作为子查询中表达式使用的当前限制的解决方法，您可以复制它。
hadoop01 :) WITH ['hello'] AS hello SELECT hello,
            * FROM ( WITH ['hello'] AS hello SELECT hello );
~~~输出参数
┌─hello─────┬─hello─────┐
│ ['hello'] │ ['hello'] │
└───────────┴───────────┘

---

 

 

 

 

 

 

 

 

 

---

Walter Savage Landor:strove with none,for none was worth my strife.Nature I loved and, next to Nature, Art:I warm'd both hands before the fire of life.It sinks, and I am ready to depart

                                                                                                                                                   ——W.S.Landor

---