ClickHouse with as简单用法

最新推荐文章于 2024-03-13 14:15:12 发布

肖艾๓

最新推荐文章于 2024-03-13 14:15:12 发布

阅读量629

点赞数 9

文章标签： clickhouse

本文链接：https://blog.csdn.net/weixin_44830864/article/details/135978637

版权

需要临时表去做统计的话，我们可能会想到with，以下是简单的with用法

一.取单值

1.求年份是1986的时间的占比
WITH (
        SELECT count()
        FROM salaries
        WHERE toYear(from_date) = 1986
    ) AS t1
SELECT round(t1 / count(), 2)
FROM salaries

2.分别求from_date 和 to_date的占比
WITH
    (
        SELECT count()
        FROM salaries
        WHERE toYear(from_date) = 1986
    ) AS t1,
    (
        SELECT count()
        FROM salaries
        WHERE toYear(to_date) = 1986
    ) AS t2
SELECT
    t1 / count(),
    t2 / count()
FROM salaries

二.取多值，这里我就用一个表演示一下

####一个表join
WITH t1 AS
    (
        SELECT
            emp_no,
            salary
        FROM salaries
    )
SELECT *
FROM salaries AS t2
INNER JOIN t1 ON t1.emp_no = t2.emp_no
LIMIT 20

Query id: db68570b-130d-4e35-8e06-fda4fb9e92b4

┌─emp_no─┬─salary─┬──from_date─┬────to_date─┬─t1.emp_no─┬─t1.salary─┐
│  10001 │  60117 │ 1986-06-26 │ 1987-06-26 │     10001 │     60117 │
│  10001 │  60117 │ 1986-06-26 │ 1987-06-26 │     10001 │     85097 │
│  10001 │  60117 │ 1986-06-26 │ 1987-06-26 │     10001 │     88958 │
│  10001 │  60117 │ 1986-06-26 │ 1987-06-26 │     10001 │     75994 │
│  10001 │  60117 │ 1986-06-26 │ 1987-06-26 │     10001 │     76884 │
│  10001 │  60117 │ 1986-06-26 │ 1987-06-26 │     10001 │     80013 │
│  10001 │  60117 │ 1986-06-26 │ 1987-06-26 │     10001 │     81025 │
│  10001 │  60117 │ 1986-06-26 │ 1987-06-26 │     10001 │     81097 │
│  10001 │  60117 │ 1986-06-26 │ 1987-06-26 │     10001 │     84917 │
│  10001 │  60117 │ 1986-06-26 │ 1987-06-26 │     10001 │     85112 │
│  10001 │  60117 │ 1986-06-26 │ 1987-06-26 │     10001 │     62102 │
│  10001 │  60117 │ 1986-06-26 │ 1987-06-26 │     10001 │     66074 │
│  10001 │  60117 │ 1986-06-26 │ 1987-06-26 │     10001 │     66596 │
│  10001 │  60117 │ 1986-06-26 │ 1987-06-26 │     10001 │     66961 │
│  10001 │  60117 │ 1986-06-26 │ 1987-06-26 │     10001 │     71046 │
│  10001 │  60117 │ 1986-06-26 │ 1987-06-26 │     10001 │     74333 │
│  10001 │  60117 │ 1986-06-26 │ 1987-06-26 │     10001 │     75286 │
│  10001 │  62102 │ 1987-06-26 │ 1988-06-25 │     10001 │     60117 │
│  10001 │  62102 │ 1987-06-26 │ 1988-06-25 │     10001 │     85097 │
│  10001 │  62102 │ 1987-06-26 │ 1988-06-25 │     10001 │     88958 │
└────────┴────────┴────────────┴────────────┴───────────┴───────────┘


###两个表join
WITH
    t1 AS
    (
        SELECT
            emp_no,
            salary
        FROM salaries
    ),
    t3 AS
    (
        SELECT
            emp_no,
            from_date
        FROM salaries
    )
SELECT *
FROM salaries AS t2
INNER JOIN t1 ON t1.emp_no = t2.emp_no
INNER JOIN t3 ON t1.emp_no = t3.emp_no
LIMIT 5

Query id: 749d1d3d-ae77-43f2-88b7-fc88f9ce2bb7

┌─t2.emp_no─┬─t2.salary─┬─t2.from_date─┬─t2.to_date─┬─t1.emp_no─┬─t1.salary─┬─t3.emp_no─┬─t3.from_date─┐
│    286476 │     57757 │   1992-01-03 │ 1993-01-02 │    286476 │     57757 │    286476 │   1992-01-03 │
│    286476 │     57757 │   1992-01-03 │ 1993-01-02 │    286476 │     57757 │    286476 │   2000-01-01 │
│    286476 │     57757 │   1992-01-03 │ 1993-01-02 │    286476 │     57757 │    286476 │   2000-12-31 │
│    286476 │     57757 │   1992-01-03 │ 1993-01-02 │    286476 │     57757 │    286476 │   2001-12-31 │
│    286476 │     57757 │   1992-01-03 │ 1993-01-02 │    286476 │     57757 │    286476 │   1988-01-04 │
└───────────┴───────────┴──────────────┴────────────┴───────────┴───────────┴───────────┴──────────────┘