ClickHouse Benchmark: 宽表 vs 半宽表IN vs 多表JOIN (单表15亿数据)

Sen Du | | 22 min read

目录

  1. 前言
  2. 结论
  3. 测试准备
  4. 压测结果
  5. SQL样本

1.前言

前段时间我们用 ClickHouse 替换了 Greenplum，成功的把一个报表模块的响应时间提高了10倍+、机器资源减少了50%。上线已经几个月了，在响应时间、系统稳定性方面，用户都很满意。不过 ClickHouse 也不是尽善尽美，在实际使用中还是存在一些问题：

为了充分利用 clickhouse 的优势，我是把多表  join 之后的数据，存储在一张宽表中，查询全走宽表。
  1. 维度过滤：当用户修改了一些维度列，因为历史数据没办法(hard)更新，修改的维度只能应用到新的数据上，导致用户基于该维度的过滤不准确。例如：商品是原始维度表，商品会有分类，用户更换了部分商品的分类 ，当使用类别过滤时，会发现不在新分类的数据也会统计在一起，或是基于新分类的数据会包含不该存在的商品。
  2. 维度聚合：和第一种情况比较类似，当用户修改了一些维度列后，按年、月、周汇总时，本来应该汇总成一条的数据，会变成多条。例如：商铺是原始维度表，商铺会有标签，用户修改了商铺标签，会导致 Group by <年>,<商铺标签>,<商铺>... 时，这家商铺的汇总数据被分成了两条。（虽然从数据挖掘的角度，这确实应该是两条数据，但是按照业务来说，用户希望一个商铺应该汇总成一条数据，标签对他们来说是为了更好的管理商铺）
  3. 数据重算：🐛(BUG) 总会有的，所以当上游的数据错了，我想把这一小撮有问题的数据修正，对 clickhouse 来说难度就会比较大。因为在 clickhouse 中会有一些基础数据表，通过物化视图实时固化成很多预计算表，更新基础表数据，物化视图不会随着变化。所以我需要一个表一个表的修改数据，而且update操作会对 clickhouse 造成较大压力。

为了能够解决(huǎn jiě)上面遇到的问题，我们准备探索一些新的方式，比如：采用 Join 的方式，把维表和事实表分离，用户的修改不会影响最终的统计结果；采用IN的操作，先在维表中过滤数据，用事实表IN(过滤到的维表ID)。Join 和 In 肯定会有性能损耗，我们需要压测得到性能差异，看业务是否可接受（技术能力和业务能力取得一个平衡）。

这次会从三个方面对比 ClickHouse 的性能：大宽表 vs 小宽表(部分列过滤用IN操作) vs 多表JOIN

2.结论

  1. 相同业务场景，在并发较低、数据扫描范围小的情况下："小宽表IN" 的性能是 "大宽表" 的2倍+，是 "多表JOIN" 的5倍+
  2. 相同业务场景，在并发较低、数据扫描范围大的情况下："小宽表IN" 的性能是 "大宽表" 的4倍+，是 "多表JOIN" 的40倍+
  3. 相同业务场景，在并发较高、数据扫描范围小的情况下："小宽表IN" 的性能是 "大宽表" 的5倍+，是 "多表JOIN" 的50倍+
  4. 相同业务场景，在并发较高、数据扫描范围大的情况下："小宽表IN" 的性能是 "大宽表" 的7倍+，是 "多表JOIN" 的100倍+
  5. "小宽表IN" 和 "大宽表"  对CPU等资源的消耗都远小于"多表JOIN"
  6. "多表JOIN"性能太差，墙裂不建议用于生产环境
* 为了缓解遇到的问题（维表数据变化导致统计有误），同时为了提高查询性能，建议使用小宽表IN设计，非必须汇总列，可以先在维表中过滤，然后用事实表IN(维表ID)

3.测试准备

1. 数据集

  • 数据总量：430 GB
  • 1个事实表：15 亿数据（26列）
  • 2个纬度表：
    • 纬度表B：8000 数据（45列）
    • 纬度表C：1000 数据（52列）

2. 机器配置

  • ClickHouse
    • 主节点：16C、64G、2.5TB HDD

3. 模型设计

  • 宽表结构：将维度表和事实表的数据聚合后，利用 materialized view 特性实时固化到宽表中
  • 半宽表IN：将维度表和事实表的数据聚合后，只把需要 Group by 和 SUM 的列放入宽表，其余过滤类，通过为维表过滤+事实表IN操作
  • 多表JOIN：只把事实表的数据，按年、月、周汇总，放入不同事实表，减少数据量

4. 查询条件

  • Q1: 动态时间区间 < 30 天；动态店铺ID；动态Limit Offset；
  • Q2: 动态时间区间 < 30 天；动态店铺ID；更多动态Where条件；动态Limit Offset；
  • Q3: 动态时间区间 > 30 天、< 180天；动态店铺ID；动态Limit Offset；
  • Q4: 动态时间区间 > 30 天、< 180天；动态店铺ID；更多动态Where条件；动态Limit Offset；
  • Q5: 动态时间区间 < 30 天；标签汇总；动态店铺ID；动态Limit Offset；
  • Q6: 动态时间区间 < 30 天；标签汇总；动态店铺ID；更多动态Where条件；动态Limit Offset；
  • Q7: 动态时间区间 > 30 天、< 180天；标签汇总；动态店铺ID；动态Limit Offset；
  • Q8: 动态时间区间 > 30 天、< 180天；标签汇总；动态店铺ID；更多动态Where条件；动态Limit Offset；

5. 压测步骤

  • 使用 Golang 语言开发程序，生成查询SQL、请求数据库、提供统一的 API 接口
  • 使用 Jmeter 调用 Golang 提供的 Rest API 接口
  • 压测前，使用 Jmeter 单线程模式，循环调用10次 x 3组查询API ，预热系统
  • 压测中，使用 Jmeter 1并发、10并发、30并发、50并发、100并发 模式测试，每种并发，循环 3 次
  • 压测后，收集监控到的请求响应数据
    • 平均响应时间
    • 95% Line

6. 结果可视化

  • 使用 Python + plotly + pandas 生成图表

4.压测结果

1并发

10并发

30并发

50并发

100并发

5.SQL样本(已脱敏)

宽表结构

SELECT
    tmp03_id
FROM
    tmp02_view PREWHERE tmp02_view.bus_date >= date '2020-10-01'
    AND tmp02_view.bus_date < date '2020-10-08'
    AND tmp02_view.store_id = 3850145954309079041
    AND tmp02_view.tmp01_status = 'ENABLED'
    AND tmp02_view.tmp13 = 'FOOD'
    AND tmp03_id > 0
GROUP BY
    tmp03_id
HAVING
    1 = 1
ORDER BY
    tmp03_id;

SELECT
    TT.date,
    TT.tmp00_id,
    TT.tmp00_name,
    TT.tmp00_code,
    TT.tmp00_name_1,
    TT.tmp00_name_2,
    TT.tmp01_id,
    TT.tmp01_name,
    TT.tmp01_code,
    TT.status,
    TT.tmp03_id,
    TT.tmp03_name,
    TT.tmp13,
    TT.tmp13,
    TT.tmp13,
    TT.spec,
    TT.stocktake_unit_id,
    TT.stocktake_unit_name,
    TT.display_order,
    TT.tmp12,
    TT.tmp12,
    TT.tmp15,
    TT.tmp16,
    TT.tmp12,
    TT.tmp12,
    TT.tmp12,
    TT.tmp11,
    TT.tmp11,
    TT.tmp11,
    TT.tmp11,
    TT.tmp11,
    TT.tmp11,
    TT.tmp11,
    TT.tmp11,
    TT.tmp11,
    TT.tmp11
FROM
    (
        SELECT
            date,
            tmp00_id,
            tmp00_name,
            tmp00_code,
            tmp00_name_1,
            tmp00_name_2,
            T.tmp01_id,
            T.tmp01_name,
            T.tmp01_code,
            T.status,
            T.tmp03_id,
            T.tmp03_name,
            T.tmp13,
            T.tmp13,
            T.tmp13,
            T.spec,
            T.stocktake_unit_id,
            T.stocktake_unit_name,
            T.display_order,
            toString(T.rate) AS rate,
            toString(T.cost_price) AS cost_price,
            toString(T.tmp12) AS tmp12,
            toString(T.tmp12) AS tmp12,
            toString(T.tmp15) AS tmp15,
            toString(T.tmp16) AS tmp16,
            toString(T.tmp12) AS tmp12,
            toString(T.tmp12) AS tmp12,
            toString(T.tmp12) AS tmp12,
            toString(T.tmp11) AS tmp11,
            toString(T.tmp11) AS tmp11,
            toString(T.tmp11 * T.rate) AS tmp11,
            toString(
                (
                    T.tmp11 + T.tmp12 - T.tmp12
                ) * T.rate
            ) AS tmp11,
            toString(
                (
                    T.tmp11 + T.tmp12 - T.tmp12 - T.tmp11 - T.tmp11
                ) * T.rate
            ) AS tmp11,
            toString(T.tmp11 * T.rate) AS tmp11,
            toString(T.tmp11 * T.cost_price_rate) AS tmp11,
            toString(
                (
                    T.tmp11 + T.tmp12 - T.tmp12
                ) * T.cost_price_rate
            ) AS tmp11,
            toString(
                (
                    T.tmp11 + T.tmp12 - T.tmp12 - T.tmp11 - T.tmp11
                ) * T.cost_price_rate
            ) AS tmp11,
            toString(T.tmp11 * T.cost_price_rate) AS tmp11,
            toString(
                T.tmp11 + T.tmp12 - T.tmp12 - T.tmp11 - T.tmp11
            ) AS tmp11,
            toString(
                T.tmp11 + T.tmp12 - T.tmp12
            ) AS tmp11
        FROM
            (
                SELECT
                    toString(bus_date) AS date,
                    store_id AS tmp00_id,
                    store_name AS tmp00_name,
                    store_code AS tmp00_code,
                    tmp00_name AS tmp00_name_1,
                    tmp00_1_name AS tmp00_name_2,
                    tmp01_id AS tmp01_id,
                    tmp01_name AS tmp01_name,
                    tmp01_code AS tmp01_code,
                    tmp03_id AS tmp03_id,
                    tmp03_name AS tmp03_name,
                    tmp13 AS tmp13,
                    (
                        CASE
                            tmp13
                            WHEN 'tmp17' THEN 'tmp18'
                            WHEN 'tmp17' THEN 'tmp18'
                            WHEN 'tmp17' THEN 'tmp18'
                            ELSE '-'
                        END
                    ) AS tmp13,
                    (
                        CASE
                            tmp13
                            WHEN 'tmp17' THEN 'tmp18'
                            WHEN 'tmp17' THEN 'tmp18'
                            ELSE '-'
                        END
                    ) AS tmp13,
                    tmp01_status AS status,
                    tmp01_spec AS spec,
                    display_order AS display_order,
                    stocktake_unit_id AS stocktake_unit_id,
                    stocktake_unit_name AS stocktake_unit_name,
                    tmp02_view.rate AS rate,
                    SUM(tmp02_view.cost_price) AS cost_price_sum,
                    SUM(tmp02_view.nonzero_cost_price) AS nonzero_cost_price_sum,
                    intDivOrZero(cost_price_sum, nonzero_cost_price_sum) AS cost_price,
                    round(cost_price * tmp02_view.rate, 8) AS cost_price_rate,
                    round(
                        SUM(tmp02_view.tmp12) / tmp02_view.rate,
                        8
                    ) AS tmp12,
                    round(
                        SUM(tmp02_view.tmp12) / tmp02_view.rate,
                        8
                    ) AS tmp12,
                    round(
                        (
                            SUM(tmp02_view.tmp16) - SUM(tmp02_view.tmp15)
                        ) / tmp02_view.rate,
                        8
                    ) AS tmp11,
                    round(
                        SUM(tmp02_view.tmp16) / tmp02_view.rate,
                        8
                    ) AS tmp11,
                    round(
                        (
                            SUM(tmp02_view.tmp15) - SUM(tmp02_view.tmp16)
                        ) / tmp02_view.rate,
                        8
                    ) AS tmp12,
                    round(
                        (
                            SUM(tmp02_view.tmp15) - SUM(tmp02_view.tmp16)
                        ) / tmp02_view.rate,
                        8
                    ) AS tmp12,
                    round(
                        SUM(tmp02_view.tmp15) / tmp02_view.rate,
                        8
                    ) AS tmp15,
                    round(
                        SUM(tmp02_view.tmp16) / tmp02_view.rate,
                        8
                    ) AS tmp16,
                    round(
                        (
                            SUM(tmp02_view.tmp15) - SUM(tmp02_view.tmp16)
                        ) / tmp02_view.rate,
                        8
                    ) AS tmp12,
                    round(
                        (
                            SUM(tmp02_view.tmp15) - SUM(tmp02_view.tmp16) + SUM(tmp02_view.tmp15) - SUM(tmp02_view.tmp16) - (
                                SUM(tmp02_view.tmp15) - SUM(tmp02_view.tmp16)
                            )
                        ) / tmp02_view.rate,
                        8
                    ) AS tmp11
                FROM
                    tmp02_view PREWHERE tmp02_view.bus_date >= date '2020-10-01'
                    AND tmp02_view.bus_date < date '2020-10-08'
                    AND tmp02_view.store_id = 3850145954309079041
                    AND tmp02_view.tmp03_id = -1
                    AND tmp02_view.tmp01_status = 'ENABLED'
                    AND tmp02_view.tmp13 = 'FOOD'
                GROUP BY
                    bus_date,
                    store_id,
                    store_name,
                    store_code,
                    tmp00_name,
                    tmp00_1_name,
                    tmp02_view.tmp01_id,
                    tmp02_view.tmp01_name,
                    tmp02_view.tmp01_code,
                    tmp02_view.tmp13,
                    tmp02_view.tmp13,
                    tmp02_view.tmp13,
                    tmp02_view.tmp01_status,
                    tmp02_view.tmp01_spec,
                    tmp02_view.tmp03_id,
                    tmp02_view.tmp03_name,
                    tmp02_view.display_order,
                    tmp02_view.stocktake_unit_id,
                    tmp02_view.stocktake_unit_name,
                    tmp02_view.rate
                HAVING
                    1 = 1
                ORDER BY
                    bus_date DESC,
                    store_id DESC,
                    tmp02_view.display_order DESC,
                    tmp02_view.tmp03_id DESC,
                    tmp02_view.tmp01_id DESC
            ) T
        UNION
        ALL
        SELECT
            date,
            tmp00_id,
            tmp00_name,
            tmp00_code,
            tmp00_name_1,
            tmp00_name_2,
            T.tmp01_id,
            T.tmp01_name,
            T.tmp01_code,
            T.status,
            T.tmp03_id,
            T.tmp03_name,
            T.tmp13,
            T.tmp13,
            T.tmp13,
            T.spec,
            T.stocktake_unit_id,
            T.stocktake_unit_name,
            T.display_order,
            toString(T.rate) AS rate,
            toString(T.cost_price) AS cost_price,
            toString(T.tmp12) AS tmp12,
            toString(T.tmp12) AS tmp12,
            toString(T.tmp15) AS tmp15,
            toString(T.tmp16) AS tmp16,
            toString(T.tmp12) AS tmp12,
            toString(T.tmp12) AS tmp12,
            toString(T.tmp12) AS tmp12,
            toString(T.tmp11) AS tmp11,
            toString(T.tmp11) AS tmp11,
            toString(T.tmp11 * T.rate) AS tmp11,
            toString(
                (
                    T.tmp11 + T.tmp12 - T.tmp12
                ) * T.rate
            ) AS tmp11,
            toString(
                (
                    T.tmp11 + T.tmp12 - T.tmp12 - T.tmp11 - T.tmp11
                ) * T.rate
            ) AS tmp11,
            toString(T.tmp11 * T.rate) AS tmp11,
            toString(T.tmp11 * T.cost_price_rate) AS tmp11,
            toString(
                (
                    T.tmp11 + T.tmp12 - T.tmp12
                ) * T.cost_price_rate
            ) AS tmp11,
            toString(
                (
                    T.tmp11 + T.tmp12 - T.tmp12 - T.tmp11 - T.tmp11
                ) * T.cost_price_rate
            ) AS tmp11,
            toString(T.tmp11 * T.cost_price_rate) AS tmp11,
            toString(
                T.tmp11 + T.tmp12 - T.tmp12 - T.tmp11 - T.tmp11
            ) AS tmp11,
            toString(
                T.tmp11 + T.tmp12 - T.tmp12
            ) AS tmp11
        FROM
            (
                SELECT
                    toString(bus_date) AS date,
                    store_id AS tmp00_id,
                    store_name AS tmp00_name,
                    store_code AS tmp00_code,
                    tmp00_name AS tmp00_name_1,
                    tmp00_1_name AS tmp00_name_2,
                    tmp01_id AS tmp01_id,
                    tmp01_name AS tmp01_name,
                    tmp01_code AS tmp01_code,
                    tmp03_id AS tmp03_id,
                    tmp03_name AS tmp03_name,
                    tmp13 AS tmp13,
                    (
                        CASE
                            tmp13
                            WHEN 'tmp17' THEN 'tmp18'
                            WHEN 'tmp17' THEN 'tmp18'
                            WHEN 'tmp17' THEN 'tmp18'
                            ELSE '-'
                        END
                    ) AS tmp13,
                    (
                        CASE
                            tmp13
                            WHEN 'tmp17' THEN 'tmp18'
                            WHEN 'tmp17' THEN 'tmp18'
                            ELSE '-'
                        END
                    ) AS tmp13,
                    tmp01_status AS status,
                    tmp01_spec AS spec,
                    display_order AS display_order,
                    stocktake_unit_id AS stocktake_unit_id,
                    stocktake_unit_name AS stocktake_unit_name,
                    tmp02_view.rate AS rate,
                    SUM(tmp02_view.cost_price) AS cost_price_sum,
                    SUM(tmp02_view.nonzero_cost_price) AS nonzero_cost_price_sum,
                    intDivOrZero(cost_price_sum, nonzero_cost_price_sum) AS cost_price,
                    round(cost_price * tmp02_view.rate, 8) AS cost_price_rate,
                    round(
                        SUM(tmp02_view.tmp12) / tmp02_view.rate,
                        8
                    ) AS tmp12,
                    round(
                        SUM(tmp02_view.tmp12) / tmp02_view.rate,
                        8
                    ) AS tmp12,
                    round(
                        (
                            SUM(tmp02_view.tmp16) - SUM(tmp02_view.tmp15)
                        ) / tmp02_view.rate,
                        8
                    ) AS tmp11,
                    round(
                        SUM(tmp02_view.tmp16) / tmp02_view.rate,
                        8
                    ) AS tmp11,
                    round(
                        (
                            SUM(tmp02_view.tmp15) - SUM(tmp02_view.tmp16)
                        ) / tmp02_view.rate,
                        8
                    ) AS tmp12,
                    round(
                        (
                            SUM(tmp02_view.tmp15) - SUM(tmp02_view.tmp16)
                        ) / tmp02_view.rate,
                        8
                    ) AS tmp12,
                    round(
                        SUM(tmp02_view.tmp15) / tmp02_view.rate,
                        8
                    ) AS tmp15,
                    round(
                        SUM(tmp02_view.tmp16) / tmp02_view.rate,
                        8
                    ) AS tmp16,
                    round(
                        (
                            SUM(tmp02_view.tmp15) - SUM(tmp02_view.tmp16)
                        ) / tmp02_view.rate,
                        8
                    ) AS tmp12,
                    round(
                        (
                            SUM(tmp02_view.tmp15) - SUM(tmp02_view.tmp16) + SUM(tmp02_view.tmp15) - SUM(tmp02_view.tmp16) - (
                                SUM(tmp02_view.tmp15) - SUM(tmp02_view.tmp16)
                            )
                        ) / tmp02_view.rate,
                        8
                    ) AS tmp11
                FROM
                    tmp02_view PREWHERE tmp02_view.bus_date >= date '2020-10-01'
                    AND tmp02_view.bus_date < date '2020-10-08'
                    AND tmp02_view.store_id = 3850145954309079041
                    AND tmp02_view.tmp03_id = 0
                    AND tmp02_view.tmp01_status = 'ENABLED'
                    AND tmp02_view.tmp13 = 'FOOD'
                GROUP BY
                    bus_date,
                    store_id,
                    store_name,
                    store_code,
                    tmp00_name,
                    tmp00_1_name,
                    tmp02_view.tmp01_id,
                    tmp02_view.tmp01_name,
                    tmp02_view.tmp01_code,
                    tmp02_view.tmp13,
                    tmp02_view.tmp13,
                    tmp02_view.tmp13,
                    tmp02_view.tmp01_status,
                    tmp02_view.tmp01_spec,
                    tmp02_view.tmp03_id,
                    tmp02_view.tmp03_name,
                    tmp02_view.display_order,
                    tmp02_view.stocktake_unit_id,
                    tmp02_view.stocktake_unit_name,
                    tmp02_view.rate
                HAVING
                    1 = 1
                ORDER BY
                    bus_date DESC,
                    store_id DESC,
                    tmp02_view.display_order DESC,
                    tmp02_view.tmp03_id DESC,
                    tmp02_view.tmp01_id DESC
                LIMIT
                    10 OFFSET 0
            ) T
    ) TT ] DEBU [0002] ### GenerateQuerySQLWithtmp03ByCH.TotalSQL:[
SELECT
    COUNT(1) AS "count"
FROM
    (
        SELECT
            1
        FROM
            tmp02_view PREWHERE tmp02_view.bus_date >= date '2020-10-01'
            AND tmp02_view.bus_date < date '2020-10-08'
            AND tmp02_view.store_id = 3850145954309079041
            AND tmp02_view.tmp03_id = 0
            AND tmp02_view.tmp01_status = 'ENABLED'
            AND tmp02_view.tmp13 = 'FOOD'
        GROUP BY
            bus_date,
            store_id,
            store_name,
            store_code,
            tmp00_name,
            tmp00_1_name,
            tmp02_view.tmp01_id,
            tmp02_view.tmp01_name,
            tmp02_view.tmp01_code,
            tmp02_view.tmp13,
            tmp02_view.tmp13,
            tmp02_view.tmp13,
            tmp02_view.tmp01_status,
            tmp02_view.tmp01_spec,
            tmp02_view.tmp03_id,
            tmp02_view.tmp03_name,
            tmp02_view.display_order,
            tmp02_view.stocktake_unit_id,
            tmp02_view.stocktake_unit_name,
            tmp02_view.rate
        HAVING
            1 = 1
        UNION
        ALL
        SELECT
            1
        FROM
            tmp02_view PREWHERE tmp02_view.bus_date >= date '2020-10-01'
            AND tmp02_view.bus_date < date '2020-10-08'
            AND tmp02_view.store_id = 3850145954309079041
            AND tmp02_view.tmp03_id = -1
            AND tmp02_view.tmp01_status = 'ENABLED'
            AND tmp02_view.tmp13 = 'FOOD'
            AND tmp03_id > 0
        GROUP BY
            tmp03_id
        HAVING
            1 = 1
    );

半宽表IN

SELECT
	TT.*
FROM(
    SELECT

    date,
    tmp05_id, tmp05_name, tmp05_code, tmp05_name_1, tmp05_name_2,
    T.tmp04_id,
    T.tmp04_name,
    T.tmp04_code,
    T.status,
    T.tag_id,
    T.tag_name,
    T.tmp06,
    T.tmp06,
    T.tmp06,
    T.spec,
    T.tmp07,
    T.tmp07,
    toString(T.tmp02) AS tmp02,
    toString(T.cost_price) AS cost_price,
    toString(T.tmp00) AS tmp00,
    toString(T.tmp00) AS tmp00,
    toString(T.tmp09) AS tmp09,
    toString(T.tmp10) AS tmp10,
    toString(T.tmp00) AS tmp00,
    toString(T.tmp00) AS tmp00,
    toString(T.tmp00) AS tmp00,
    toString(T.tmp01) AS tmp01,
    toString(T.tmp01) AS tmp01,
    toString(T.tmp01 * T.tmp02) AS tmp01,
    toString((T.tmp01 + T.tmp00 - T.tmp00) * T.tmp02) AS tmp01,
    toString(
        (
            T.tmp01 + T.tmp00 - T.tmp00 - T.tmp01 - T.tmp01
        ) * T.tmp02
    ) AS tmp01,
    toString(T.tmp01 * T.tmp02) AS tmp01,
    toString(T.tmp01 * T.tmp02) AS tmp01,
    toString((T.tmp01 + T.tmp00 - T.tmp00) * T.tmp02) AS tmp01,
    toString(
        (
            T.tmp01 + T.tmp00 - T.tmp00 - T.tmp01 - T.tmp01
        ) * T.tmp02
    ) AS tmp01,
    toString(T.tmp01 * T.tmp02) AS tmp01,
    toString(
        T.tmp01 + T.tmp00 - T.tmp00 - T.tmp01 - T.tmp01
    ) AS tmp01,
    toString(T.tmp01 + T.tmp00 - T.tmp00) AS tmp01


    FROM(
        SELECT

        toString(bus_date) AS date,
        tmp01.store_id AS tmp05_id, tmp01.store_name AS tmp05_name,
        tmp01.store_code AS tmp05_code, tmp01.tmp05_name AS tmp05_name_1,
        tmp01.tmp05_1_name AS tmp05_name_2,
        tmp04_id AS tmp04_id,
        tmp04_name AS tmp04_name,
        tmp04_code AS tmp04_code,
        tag_id AS tag_id,
        tag_name AS tag_name,
        tmp06 AS tmp06,
        (CASE tmp06 WHEN 'tmp11'
            THEN 'tmp12'
            WHEN 'tmp11'
            THEN 'tmp12'
            WHEN 'tmp11'
            THEN 'tmp12'
            ELSE '-'
            END) AS tmp06,
        (CASE tmp06 WHEN 'tmp11'
            THEN 'tmp12'
            WHEN 'tmp11'
            THEN 'tmp12'
            WHEN 'tmp11'
            THEN 'tmp12'
            ELSE '-'
            END) AS tmp06,
        tmp04_status AS status,
        tmp04_spec AS spec,
        tmp07 AS tmp07,
        tmp07 AS tmp07,
        tmp01.tmp02 AS tmp02,
        SUM(tmp01.cost_price) AS cost_price_sum,
        SUM(tmp01.nonzero_cost_price) AS nonzero_cost_price_sum,
        intDivOrZero(cost_price_sum, nonzero_cost_price_sum) AS cost_price,
        round(
            cost_price * tmp01.tmp02, 8
        ) AS tmp02,
        round(
            SUM(tmp01.tmp00) / tmp01.tmp02, 8
        ) AS tmp00,
        round(
            SUM(tmp01.tmp00) / tmp01.tmp02, 8
        ) AS tmp00,
        round(
            (SUM(tmp01.tmp10) - SUM(tmp01.tmp09)) / tmp01.tmp02, 8
        ) AS tmp01,
        round(
            SUM(tmp01.tmp10) / tmp01.tmp02, 8
        ) AS tmp01,
        round(
            (SUM(tmp01.tmp09) - SUM(tmp01.tmp10)) / tmp01.tmp02, 8
        ) AS tmp00,
        round(
            (SUM(tmp01.tmp09) - SUM(tmp01.tmp10)) / tmp01.tmp02, 8
        ) AS tmp00,
        round(
            SUM(tmp01.tmp09) / tmp01.tmp02, 8
        ) AS tmp09,
        round(
            SUM(tmp01.tmp10) / tmp01.tmp02, 8
        ) AS tmp10,
        round(
            (SUM(tmp01.tmp09) - SUM(tmp01.tmp10)) / tmp01.tmp02, 8
        ) AS tmp00,
        round(
            (SUM(tmp01.tmp09) - SUM(tmp01.tmp10) +
                SUM(tmp01.tmp09) - SUM(tmp01.tmp10) -
                (SUM(tmp01.tmp09) - SUM(tmp01.tmp10))) / tmp01.tmp02, 8
        ) AS tmp01

        FROM tmp01 PREWHERE

        tmp01.bus_date >= date '2020-05-11'
        AND tmp01.bus_date < date '2020-07-12'
        AND tmp01.store_id = 3850146094847623169 AND tmp01.tmp04_id = 4150920744698355713

        AND tmp01.tmp04_id IN(
            SELECT id FROM tmp04_view WHERE(tmp04_view.category_id = 3895644930529886116 OR tmp04_view.category_1_id = 3895644930529886116 OR tmp04_view.category_2_id = 3895644930529886116 OR tmp04_view.category_3_id = 3895644930529886116 OR tmp04_view.category_4_id = 3895644930529886116)
        ) AND tmp01.tag_id = -1



        AND tmp01.tmp04_id IN(
            SELECT id FROM tmp04_view WHERE tmp04_view.tmp06 = 'PURCHASE') AND tmp01.tmp04_id IN(
            SELECT id FROM tmp04_view WHERE tmp04_view.status = 'ENABLED') AND tmp01.tmp04_id IN(
            SELECT id FROM tmp04_view WHERE tmp04_view.display_order < 4000)

        GROUP BY

        bus_date,
        tmp01.store_id, tmp01.store_name, tmp01.store_code,
        tmp01.tmp05_name, tmp01.tmp05_1_name,
        tmp01.tmp04_id,
        tmp01.tmp04_name,
        tmp01.tmp04_code,
        tmp01.tmp06,
        tmp01.tmp06,
        tmp01.tmp06,
        tmp01.tmp04_status,
        tmp01.tmp04_spec,
        tmp01.tag_id,
        tmp01.tag_name,
        tmp01.tmp07,
        tmp01.tmp07,
        tmp01.tmp02 HAVING 1 = 1 AND SUM(tmp01.tmp00) != 0 AND SUM(tmp01.tmp00) != 0 ORDER BY

        bus_date DESC,
        tmp01.store_id DESC,
        tmp01.tag_id DESC,
        tmp01.tmp04_id DESC

    ) T UNION ALL SELECT

    date,
    tmp05_id, tmp05_name, tmp05_code, tmp05_name_1, tmp05_name_2,
    T.tmp04_id,
    T.tmp04_name,
    T.tmp04_code,
    T.status,
    T.tag_id,
    T.tag_name,
    T.tmp06,
    T.tmp06,
    T.tmp06,
    T.spec,
    T.tmp07,
    T.tmp07,
    toString(T.tmp02) AS tmp02,
    toString(T.cost_price) AS cost_price,
    toString(T.tmp00) AS tmp00,
    toString(T.tmp00) AS tmp00,
    toString(T.tmp09) AS tmp09,
    toString(T.tmp10) AS tmp10,
    toString(T.tmp00) AS tmp00,
    toString(T.tmp00) AS tmp00,
    toString(T.tmp00) AS tmp00,
    toString(T.tmp01) AS tmp01,
    toString(T.tmp01) AS tmp01,
    toString(T.tmp01 * T.tmp02) AS tmp01,
    toString((T.tmp01 + T.tmp00 - T.tmp00) * T.tmp02) AS tmp01,
    toString(
        (
            T.tmp01 + T.tmp00 - T.tmp00 - T.tmp01 - T.tmp01
        ) * T.tmp02
    ) AS tmp01,
    toString(T.tmp01 * T.tmp02) AS tmp01,
    toString(T.tmp01 * T.tmp02) AS tmp01,
    toString((T.tmp01 + T.tmp00 - T.tmp00) * T.tmp02) AS tmp01,
    toString(
        (
            T.tmp01 + T.tmp00 - T.tmp00 - T.tmp01 - T.tmp01
        ) * T.tmp02
    ) AS tmp01,
    toString(T.tmp01 * T.tmp02) AS tmp01,
    toString(
        T.tmp01 + T.tmp00 - T.tmp00 - T.tmp01 - T.tmp01
    ) AS tmp01,
    toString(T.tmp01 + T.tmp00 - T.tmp00) AS tmp01


    FROM(
        SELECT

        toString(bus_date) AS date,
        tmp01.store_id AS tmp05_id, tmp01.store_name AS tmp05_name,
        tmp01.store_code AS tmp05_code, tmp01.tmp05_name AS tmp05_name_1,
        tmp01.tmp05_1_name AS tmp05_name_2,
        tmp04_id AS tmp04_id,
        tmp04_name AS tmp04_name,
        tmp04_code AS tmp04_code,
        tag_id AS tag_id,
        tag_name AS tag_name,
        tmp06 AS tmp06,
        (CASE tmp06 WHEN 'tmp11'
            THEN 'tmp12'
            WHEN 'tmp11'
            THEN 'tmp12'
            WHEN 'tmp11'
            THEN 'tmp12'
            ELSE '-'
            END) AS tmp06,
        (CASE tmp06 WHEN 'tmp11'
            THEN 'tmp12'
            WHEN 'tmp11'
            THEN 'tmp12'
            WHEN 'tmp11'
            THEN 'tmp12'
            ELSE '-'
            END) AS tmp06,
        tmp04_status AS status,
        tmp04_spec AS spec,
        tmp07 AS tmp07,
        tmp07 AS tmp07,
        tmp01.tmp02 AS tmp02,
        SUM(tmp01.cost_price) AS cost_price_sum,
        SUM(tmp01.nonzero_cost_price) AS nonzero_cost_price_sum,
        intDivOrZero(cost_price_sum, nonzero_cost_price_sum) AS cost_price,
        round(
            cost_price * tmp01.tmp02, 8
        ) AS tmp02,
        round(
            SUM(tmp01.tmp00) / tmp01.tmp02, 8
        ) AS tmp00,
        round(
            SUM(tmp01.tmp00) / tmp01.tmp02, 8
        ) AS tmp00,
        round(
            (SUM(tmp01.tmp10) - SUM(tmp01.tmp09)) / tmp01.tmp02, 8
        ) AS tmp01,
        round(
            SUM(tmp01.tmp10) / tmp01.tmp02, 8
        ) AS tmp01,
        round(
            (SUM(tmp01.tmp09) - SUM(tmp01.tmp10)) / tmp01.tmp02, 8
        ) AS tmp00,
        round(
            (SUM(tmp01.tmp09) - SUM(tmp01.tmp10)) / tmp01.tmp02, 8
        ) AS tmp00,
        round(
            SUM(tmp01.tmp09) / tmp01.tmp02, 8
        ) AS tmp09,
        round(
            SUM(tmp01.tmp10) / tmp01.tmp02, 8
        ) AS tmp10,
        round(
            (SUM(tmp01.tmp09) - SUM(tmp01.tmp10)) / tmp01.tmp02, 8
        ) AS tmp00,
        round(
            (SUM(tmp01.tmp09) - SUM(tmp01.tmp10) +
                SUM(tmp01.tmp09) - SUM(tmp01.tmp10) -
                (SUM(tmp01.tmp09) - SUM(tmp01.tmp10))) / tmp01.tmp02, 8
        ) AS tmp01

        FROM tmp01 PREWHERE

        tmp01.bus_date >= date '2020-05-11'
        AND tmp01.bus_date < date '2020-07-12'
        AND tmp01.store_id = 3850146094847623169 AND tmp01.tmp04_id = 4150920744698355713

        AND tmp01.tmp04_id IN(
            SELECT id FROM tmp04_view WHERE(tmp04_view.category_id = 3895644930529886116 OR tmp04_view.category_1_id = 3895644930529886116 OR tmp04_view.category_2_id = 3895644930529886116 OR tmp04_view.category_3_id = 3895644930529886116 OR tmp04_view.category_4_id = 3895644930529886116)
        ) AND tmp01.tag_id = 0



        AND tmp01.tmp04_id IN(
            SELECT id FROM tmp04_view WHERE tmp04_view.tmp06 = 'PURCHASE') AND tmp01.tmp04_id IN(
            SELECT id FROM tmp04_view WHERE tmp04_view.status = 'ENABLED') AND tmp01.tmp04_id IN(
            SELECT id FROM tmp04_view WHERE tmp04_view.display_order < 4000)

        GROUP BY

        bus_date,
        tmp01.store_id, tmp01.store_name, tmp01.store_code,
        tmp01.tmp05_name, tmp01.tmp05_1_name,
        tmp01.tmp04_id,
        tmp01.tmp04_name,
        tmp01.tmp04_code,
        tmp01.tmp06,
        tmp01.tmp06,
        tmp01.tmp06,
        tmp01.tmp04_status,
        tmp01.tmp04_spec,
        tmp01.tag_id,
        tmp01.tag_name,
        tmp01.tmp07,
        tmp01.tmp07,
        tmp01.tmp02 HAVING 1 = 1 AND SUM(tmp01.tmp00) != 0 AND SUM(tmp01.tmp00) != 0 ORDER BY

        bus_date DESC,
        tmp01.store_id DESC,
        tmp01.tag_id DESC,
        tmp01.tmp04_id DESC

        LIMIT 10 OFFSET 0
    ) T
) TT;

SELECT COUNT(1) AS "count"
FROM (
SELECT 1
FROM tmp01
    PREWHERE
        tmp01.bus_date >= date '2020-05-11' AND tmp01.bus_date < date '2020-07-12'
        AND tmp01.store_id = 3850146094847623169
        AND tmp01.tmp04_id = 4150920744698355713
        AND tmp01.tmp04_id IN (
        SELECT id
        FROM tmp04_view
        WHERE (tmp04_view.category_id = 3895644930529886116
            OR tmp04_view.category_1_id = 3895644930529886116
            OR tmp04_view.category_2_id = 3895644930529886116
            OR tmp04_view.category_3_id = 3895644930529886116
            OR tmp04_view.category_4_id = 3895644930529886116)
    )
        AND tmp01.tag_id = 0
        AND tmp01.tmp04_id IN (
        SELECT id
        FROM tmp04_view
        WHERE tmp04_view.tmp06 = 'PURCHASE')
        AND tmp01.tmp04_id IN (
        SELECT id
        FROM tmp04_view
        WHERE tmp04_view.status = 'ENABLED')
        AND tmp01.tmp04_id IN (
        SELECT id
        FROM tmp04_view
        WHERE tmp04_view.display_order < 4000)

GROUP BY bus_date,
         tmp01.store_id, tmp01.store_name, tmp01.store_code,
         tmp01.tmp05_name, tmp01.tmp05_1_name,
         tmp01.tmp04_id,
         tmp01.tmp04_name,
         tmp01.tmp04_code,
         tmp01.tmp06,
         tmp01.tmp06,
         tmp01.tmp06,
         tmp01.tmp04_status,
         tmp01.tmp04_spec,
         tmp01.tag_id,
         tmp01.tag_name,
         tmp01.tmp07,
         tmp01.tmp07,
         tmp01.tmp02
HAVING 1 = 1
   AND SUM(tmp01.tmp00) != 0
   AND SUM(tmp01.tmp00) != 0
UNION ALL
SELECT 1
FROM tmp01
    PREWHERE
        tmp01.bus_date >= date '2020-05-11' AND tmp01.bus_date < date '2020-07-12'
        AND tmp01.store_id = 3850146094847623169
        AND tmp01.tmp04_id = 4150920744698355713
        AND tmp01.tmp04_id IN (
        SELECT id
        FROM tmp04_view
        WHERE (tmp04_view.category_id = 3895644930529886116
            OR tmp04_view.category_1_id = 3895644930529886116
            OR tmp04_view.category_2_id = 3895644930529886116
            OR tmp04_view.category_3_id = 3895644930529886116
            OR tmp04_view.category_4_id = 3895644930529886116)
    )
        AND tmp01.tag_id = -1
        AND tmp01.tmp04_id IN (
        SELECT id
        FROM tmp04_view
        WHERE tmp04_view.tmp06 = 'PURCHASE')
        AND tmp01.tmp04_id IN (
        SELECT id
        FROM tmp04_view
        WHERE tmp04_view.status = 'ENABLED')
        AND tmp01.tmp04_id IN (
        SELECT id
        FROM tmp04_view
        WHERE tmp04_view.display_order < 4000)
        AND tag_id > 0
GROUP BY tag_id
HAVING 1 = 1
   AND SUM(tmp01.tmp00) != 0
   AND SUM(tmp01.tmp00) != 0
   );

多表JOIN

SELECT TT.*
FROM (
 SELECT date,
        tmp01_id,
        tmp01_name,
        tmp01_code,
        tmp01_name_1,
        tmp01_name_2,
        T.tmp02_id,
        T.tmp02_name,
        T.tmp02_code,
        T.status,
        T.tmp03_id,
        T.tmp03_name,
        T.tmp16,
        T.tmp16,
        T.tmp16,
        T.spec,
        T.tmp08_unit_id,
        T.tmp08_unit_name,
        toString(T.rate) AS                                                           rate,
        toString(T.cost_price) AS                                                     cost_price,
        toString(T.tmp13) AS                                                tmp13,
        toString(T.tmp13) AS                                             tmp13,
        toString(T.tmp11) AS                                                  tmp11,
        toString(T.tmp12) AS                                                 tmp12,
        toString(T.tmp13) AS                                                tmp13,
        toString(T.tmp07) AS                                                   tmp07,
        toString(T.tmp06) AS                                                 tmp06,
        toString(T.tmp08_scrap_tmp09) AS                                           tmp08_scrap_tmp09,
        toString(T.tmp08_norm_tmp09) AS                                            tmp08_norm_tmp09,
        toString(T.tmp08_norm_tmp09 * T.rate) AS                                   norm_tmp09,
        toString((T.tmp_actual_tmp09 + T.tmp06 - T.tmp07) * T.rate) AS actual_tmp09,
        toString(
                    (
                            T.tmp_actual_tmp09 + T.tmp06 - T.tmp07 - T.tmp08_norm_tmp09 -
                            T.tmp08_scrap_tmp09
                        ) * T.rate
            ) AS                                                                      diff_tmp09,
        toString(T.tmp08_scrap_tmp09 * T.rate) AS                                  scrap_tmp09,
        toString(T.tmp08_norm_tmp09 * T.cost_price_rate) AS                        norm_tmp09_amount,
        toString((T.tmp_actual_tmp09 + T.tmp06 - T.tmp07) *
                 T.cost_price_rate) AS                                                actual_tmp09_amount,
        toString(
                    (
                            T.tmp_actual_tmp09 + T.tmp06 - T.tmp07 - T.tmp08_norm_tmp09 -
                            T.tmp08_scrap_tmp09
                        ) * T.cost_price_rate
            ) AS                                                                      diff_tmp09_amount,
        toString(T.tmp08_scrap_tmp09 * T.cost_price_rate) AS                       scrap_tmp09_amount,
        toString(
                    T.tmp_actual_tmp09 + T.tmp06 - T.tmp07 - T.tmp08_norm_tmp09 -
                    T.tmp08_scrap_tmp09
            ) AS                                                                      tmp08_diff_tmp09,
        toString(T.tmp_actual_tmp09 + T.tmp06 - T.tmp07) AS            tmp08_actual_tmp09
         ,
        toString(
                -(T.tmp_actual_tmp09 + T.tmp06 - T.tmp07 - T.tmp08_norm_tmp09 -
                  T.tmp08_scrap_tmp09)
            ) AS                                                                      tmp08_diff_week,
        0 AS                                                                          tmp08_diff_month,
        1 AS                                                                          tmp16,
        toString(
                -(T.tmp_actual_tmp09 + T.tmp06 - T.tmp07 - T.tmp08_norm_tmp09 -
                  T.tmp08_scrap_tmp09)
            ) AS                                                                      tmp13

 FROM (
          SELECT toString(bus_date)                                   AS date,
                 tmp05.id                                             AS tmp01_id,
                 tmp05.name_cn                                        AS tmp01_name,
                 tmp05.us_id                                          AS tmp01_code,
                 tmp05.tmp01_name                                    AS tmp01_name_1,
                 tmp05.tmp01_1_name                                  AS tmp01_name_2,
                 tmp02.id                                           AS tmp02_id,
                 tmp02.name_cn                                      AS tmp02_name,
                 tmp02.code                                         AS tmp02_code,
                 tmp02.tmp03_1                                        AS tmp03_id,
                 tmp02.tmp03_1_name                                   AS tmp03_name,
                 tmp02.tmp16                                 AS tmp16,
                 (CASE tmp02.tmp16
                      WHEN 'tmp14' THEN 'tmp15'
                      WHEN 'tmp14' THEN 'tmp15'
                      WHEN 'tmp14' THEN 'tmp15'
                      ELSE '-'
                     END)                                             AS tmp16,
                 (CASE tmp02.tmp16
                      WHEN 'tmp14' THEN 'tmp15'
                      WHEN 'tmp14' THEN 'tmp15'
                      WHEN 'tmp14' THEN 'tmp15'
                      ELSE '-'
                     END)                                             AS tmp16,
                 tmp02.status                                       AS status,
                 tmp02.spec                                         AS spec,
                 tmp04_view.tmp08_unit_id            AS tmp08_unit_id,
                 tmp04_view.tmp08_unit_name          AS tmp08_unit_name,
                 tmp04_view.rate                         AS rate,
                 sumMerge(tmp04_view.cost_price)         AS cost_price_sum,
                 sumMerge(tmp04_view.nonzero_cost_price) AS nonzero_cost_price_sum,
                 intDivOrZero(cost_price_sum, nonzero_cost_price_sum) AS cost_price,
                 round(
                         cost_price * tmp04_view.rate, 8
                     )                                                AS cost_price_rate,
                 round(
                             sumMerge(tmp04_view.tmp06) / tmp04_view.rate, 8
                     )                                                AS tmp06,
                 round(
                             sumMerge(tmp04_view.tmp07) / tmp04_view.rate, 8
                     )                                                AS tmp07,
                 round(
                             (sumMerge(tmp04_view.tmp12) -
                              sumMerge(tmp04_view.tmp11)) / tmp04_view.rate, 8
                     )                                                AS tmp08_norm_tmp09,
                 round(
                             sumMerge(tmp04_view.tmp12) / tmp04_view.rate, 8
                     )                                                AS tmp08_scrap_tmp09,
                 round(
                             (sumMerge(tmp04_view.tmp11) -
                              sumMerge(tmp04_view.tmp12)) / tmp04_view.rate, 8
                     )                                                AS tmp13,
                 round(
                             (sumMerge(tmp04_view.tmp11) -
                              sumMerge(tmp04_view.tmp12)) / tmp04_view.rate,
                             8
                     )                                                AS tmp13,
                 round(
                             sumMerge(tmp04_view.tmp11) / tmp04_view.rate, 8
                     )                                                AS tmp11,
                 round(
                             sumMerge(tmp04_view.tmp12) / tmp04_view.rate, 8
                     )                                                AS tmp12,
                 round(
                             (sumMerge(tmp04_view.tmp11) -
                              sumMerge(tmp04_view.tmp12)) / tmp04_view.rate, 8
                     )                                                AS tmp13,
                 round(
                             (sumMerge(tmp04_view.tmp11) -
                              sumMerge(tmp04_view.tmp12) +
                              sumMerge(tmp04_view.tmp11) -
                              sumMerge(tmp04_view.tmp12) -
                              (sumMerge(tmp04_view.tmp11) -
                               sumMerge(tmp04_view.tmp12))) / tmp04_view.rate,
                             8
                     )                                                AS tmp_actual_tmp09

          FROM tmp04_view LEFT JOIN tmp05
          ON tmp04_view.tmp05_id = tmp05.id
              LEFT JOIN tmp02_view AS tmp02 ON tmp04_view.tmp02_id = tmp02.id

          WHERE
              tmp04_view.bus_date >= date '2020-11-23'
            AND tmp04_view.bus_date
              < date '2020-11-30'
            AND tmp05.id = 4287535173706190848
            AND tmp02.id = 4241653511240028160

            AND tmp02.tmp03_1 = 4043666293462106113


            AND tmp02.tmp16 = 'PURCHASE'
            AND tmp02.status = 'ENABLED'
            AND tmp02.display_order
              < 4000

          GROUP BY
              bus_date,
              tmp05.id, tmp05.name_cn, tmp05.us_id,
              tmp05.tmp01_name, tmp05.tmp01_1_name,
              tmp02.id,
              tmp02.name_cn,
              tmp02.code,
              tmp02.tmp16,
              tmp02.tmp16,
              tmp02.tmp16,
              tmp02.status,
              tmp02.spec,
              tmp02.tmp03_1,
              tmp02.tmp03_1_name,
              tmp04_view.tmp08_unit_id,
              tmp04_view.tmp08_unit_name,
              tmp04_view.rate
          HAVING
              1=1
          ORDER BY
              bus_date DESC,
              tmp05.id DESC,
              tmp02.tmp03_1 DESC,
              tmp02.id DESC

          ) T
 UNION ALL
 SELECT date,
        tmp01_id,
        tmp01_name,
        tmp01_code,
        tmp01_name_1,
        tmp01_name_2,
        T.tmp02_id,
        T.tmp02_name,
        T.tmp02_code,
        T.status,
        T.tmp03_id,
        T.tmp03_name,
        T.tmp16,
        T.tmp16,
        T.tmp16,
        T.spec,
        T.tmp08_unit_id,
        T.tmp08_unit_name,
        toString(T.rate) AS                                                           rate,
        toString(T.cost_price) AS                                                     cost_price,
        toString(T.tmp13) AS                                                tmp13,
        toString(T.tmp13) AS                                             tmp13,
        toString(T.tmp11) AS                                                  tmp11,
        toString(T.tmp12) AS                                                 tmp12,
        toString(T.tmp13) AS                                                tmp13,
        toString(T.tmp07) AS                                                   tmp07,
        toString(T.tmp06) AS                                                 tmp06,
        toString(T.tmp08_scrap_tmp09) AS                                           tmp08_scrap_tmp09,
        toString(T.tmp08_norm_tmp09) AS                                            tmp08_norm_tmp09,
        toString(T.tmp08_norm_tmp09 * T.rate) AS                                   norm_tmp09,
        toString((T.tmp_actual_tmp09 + T.tmp06 - T.tmp07) * T.rate) AS actual_tmp09,
        toString(
                    (
                            T.tmp_actual_tmp09 + T.tmp06 - T.tmp07 - T.tmp08_norm_tmp09 -
                            T.tmp08_scrap_tmp09
                        ) * T.rate
            ) AS                                                                      diff_tmp09,
        toString(T.tmp08_scrap_tmp09 * T.rate) AS                                  scrap_tmp09,
        toString(T.tmp08_norm_tmp09 * T.cost_price_rate) AS                        norm_tmp09_amount,
        toString((T.tmp_actual_tmp09 + T.tmp06 - T.tmp07) *
                 T.cost_price_rate) AS                                                actual_tmp09_amount,
        toString(
                    (
                            T.tmp_actual_tmp09 + T.tmp06 - T.tmp07 - T.tmp08_norm_tmp09 -
                            T.tmp08_scrap_tmp09
                        ) * T.cost_price_rate
            ) AS                                                                      diff_tmp09_amount,
        toString(T.tmp08_scrap_tmp09 * T.cost_price_rate) AS                       scrap_tmp09_amount,
        toString(
                    T.tmp_actual_tmp09 + T.tmp06 - T.tmp07 - T.tmp08_norm_tmp09 -
                    T.tmp08_scrap_tmp09
            ) AS                                                                      tmp08_diff_tmp09,
        toString(T.tmp_actual_tmp09 + T.tmp06 - T.tmp07) AS            tmp08_actual_tmp09
         ,
        toString(
                -(T.tmp_actual_tmp09 + T.tmp06 - T.tmp07 - T.tmp08_norm_tmp09 -
                  T.tmp08_scrap_tmp09)
            ) AS                                                                      tmp08_diff_week,
        0 AS                                                                          tmp08_diff_month,
        1 AS                                                                          tmp16,
        toString(
                -(T.tmp_actual_tmp09 + T.tmp06 - T.tmp07 - T.tmp08_norm_tmp09 -
                  T.tmp08_scrap_tmp09)
            ) AS                                                                      tmp13

 FROM (
          SELECT toString(bus_date)                                   AS date,
                 tmp05.id                                             AS tmp01_id,
                 tmp05.name_cn                                        AS tmp01_name,
                 tmp05.us_id                                          AS tmp01_code,
                 tmp05.tmp01_name                                    AS tmp01_name_1,
                 tmp05.tmp01_1_name                                  AS tmp01_name_2,
                 tmp02.id                                           AS tmp02_id,
                 tmp02.name_cn                                      AS tmp02_name,
                 tmp02.code                                         AS tmp02_code,
                 tmp02.tmp03_1                                        AS tmp03_id,
                 tmp02.tmp03_1_name                                   AS tmp03_name,
                 tmp02.tmp16                                 AS tmp16,
                 (CASE tmp02.tmp16
                      WHEN 'tmp14' THEN 'tmp15'
                      WHEN 'tmp14' THEN 'tmp15'
                      WHEN 'tmp14' THEN 'tmp15'
                      ELSE '-'
                     END)                                             AS tmp16,
                 (CASE tmp02.tmp16
                      WHEN 'tmp14' THEN 'tmp15'
                      WHEN 'tmp14' THEN 'tmp15'
                      WHEN 'tmp14' THEN 'tmp15'
                      ELSE '-'
                     END)                                             AS tmp16,
                 tmp02.status                                       AS status,
                 tmp02.spec                                         AS spec,
                 tmp04_view.tmp08_unit_id            AS tmp08_unit_id,
                 tmp04_view.tmp08_unit_name          AS tmp08_unit_name,
                 tmp04_view.rate                         AS rate,
                 sumMerge(tmp04_view.cost_price)         AS cost_price_sum,
                 sumMerge(tmp04_view.nonzero_cost_price) AS nonzero_cost_price_sum,
                 intDivOrZero(cost_price_sum, nonzero_cost_price_sum) AS cost_price,
                 round(
                         cost_price * tmp04_view.rate, 8
                     )                                                AS cost_price_rate,
                 round(
                             sumMerge(tmp04_view.tmp06) / tmp04_view.rate, 8
                     )                                                AS tmp06,
                 round(
                             sumMerge(tmp04_view.tmp07) / tmp04_view.rate, 8
                     )                                                AS tmp07,
                 round(
                             (sumMerge(tmp04_view.tmp12) -
                              sumMerge(tmp04_view.tmp11)) / tmp04_view.rate, 8
                     )                                                AS tmp08_norm_tmp09,
                 round(
                             sumMerge(tmp04_view.tmp12) / tmp04_view.rate, 8
                     )                                                AS tmp08_scrap_tmp09,
                 round(
                             (sumMerge(tmp04_view.tmp11) -
                              sumMerge(tmp04_view.tmp12)) / tmp04_view.rate, 8
                     )                                                AS tmp13,
                 round(
                             (sumMerge(tmp04_view.tmp11) -
                              sumMerge(tmp04_view.tmp12)) / tmp04_view.rate,
                             8
                     )                                                AS tmp13,
                 round(
                             sumMerge(tmp04_view.tmp11) / tmp04_view.rate, 8
                     )                                                AS tmp11,
                 round(
                             sumMerge(tmp04_view.tmp12) / tmp04_view.rate, 8
                     )                                                AS tmp12,
                 round(
                             (sumMerge(tmp04_view.tmp11) -
                              sumMerge(tmp04_view.tmp12)) / tmp04_view.rate, 8
                     )                                                AS tmp13,
                 round(
                             (sumMerge(tmp04_view.tmp11) -
                              sumMerge(tmp04_view.tmp12) +
                              sumMerge(tmp04_view.tmp11) -
                              sumMerge(tmp04_view.tmp12) -
                              (sumMerge(tmp04_view.tmp11) -
                               sumMerge(tmp04_view.tmp12))) / tmp04_view.rate,
                             8
                     )                                                AS tmp_actual_tmp09

          FROM tmp04_view LEFT JOIN tmp05
          ON tmp04_view.tmp05_id = tmp05.id
              LEFT JOIN tmp02_view AS tmp02 ON tmp04_view.tmp02_id = tmp02.id

          WHERE
              tmp04_view.bus_date >= date '2020-11-23'
            AND tmp04_view.bus_date
              < date '2020-11-30'
            AND tmp05.id = 4287535173706190848
            AND tmp02.id = 4241653511240028160

            AND tmp02.tmp03_1 = 0


            AND tmp02.tmp16 = 'PURCHASE'
            AND tmp02.status = 'ENABLED'
            AND tmp02.display_order
              < 4000

          GROUP BY
              bus_date,
              tmp05.id, tmp05.name_cn, tmp05.us_id,
              tmp05.tmp01_name, tmp05.tmp01_1_name,
              tmp02.id,
              tmp02.name_cn,
              tmp02.code,
              tmp02.tmp16,
              tmp02.tmp16,
              tmp02.tmp16,
              tmp02.status,
              tmp02.spec,
              tmp02.tmp03_1,
              tmp02.tmp03_1_name,
              tmp04_view.tmp08_unit_id,
              tmp04_view.tmp08_unit_name,
              tmp04_view.rate
          HAVING
              1=1
          ORDER BY
              bus_date DESC,
              tmp05.id DESC,
              tmp02.tmp03_1 DESC,
              tmp02.id DESC
              LIMIT 9 OFFSET 0
          ) T
 ) TT;

SELECT COUNT(1) AS "count"
FROM (
    SELECT 1
    FROM tmp04_view LEFT JOIN tmp05
    ON tmp04_view.tmp05_id = tmp05.id
        LEFT JOIN tmp02_view AS tmp02 ON tmp04_view.tmp02_id = tmp02.id

    WHERE
        tmp04_view.bus_date >= date '2020-11-23'
    AND tmp04_view.bus_date
        < date '2020-11-30'
    AND tmp05.id = 4287535173706190848
    AND tmp02.id = 4241653511240028160

    AND tmp02.tmp03_1 = 0


    AND tmp02.tmp16 = 'PURCHASE'
    AND tmp02.status = 'ENABLED'
    AND tmp02.display_order
        < 4000

    GROUP BY
        bus_date,
        tmp05.id, tmp05.name_cn, tmp05.us_id,
        tmp05.tmp01_name, tmp05.tmp01_1_name,
        tmp02.id,
        tmp02.name_cn,
        tmp02.code,
        tmp02.tmp16,
        tmp02.tmp16,
        tmp02.tmp16,
        tmp02.status,
        tmp02.spec,
        tmp02.tmp03_1,
        tmp02.tmp03_1_name,
        tmp04_view.tmp08_unit_id,
        tmp04_view.tmp08_unit_name,
        tmp04_view.rate
    HAVING
        1=1
    UNION ALL
    SELECT
        1
    FROM
        tmp04_view
        LEFT JOIN tmp05
    ON tmp04_view.tmp05_id = tmp05.id
        LEFT JOIN tmp02_view AS tmp02 ON tmp04_view.tmp02_id = tmp02.id

    WHERE
        tmp04_view.bus_date >= date '2020-11-23'
    AND tmp04_view.bus_date
        < date '2020-11-30'
    AND tmp05.id = 4287535173706190848
    AND tmp02.id = 4241653511240028160

    AND tmp02.tmp03_1 = 4043666293462106113
    

    AND tmp02.tmp16 = 'PURCHASE'
    AND tmp02.status = 'ENABLED'
    AND tmp02.display_order
        < 4000

    AND tmp03_1
        > 0
    GROUP BY
        tmp03_1
    HAVING
        1=1
);

End. 👻

Share:
Show Comments