了解那些“奇葩”SQL写法，快速写出高效率SQL(2)

--选择近5天的资产来看
--常见写法，count distinct写法
SELECT
    dt
    ,COUNT(DISTINCT cst_id) AS cst_cnt
FROM @pc_bill_bal
WHERE dt BETWEEN  '${bizdate-5}' AND '${bizdate}'
GROUP BY
    dt
;
--优化写法
SELECT
    dt
    ,COUNT(cst_id) AS cst_cnt
FROM    (
    SELECT
        dt
        ,cst_id
    FROM @pc_bill_bal
    WHERE dt BETWEEN  '${bizdate-5}' AND '${bizdate}'
    GROUP BY
        dt
        ,cst_id
)base
GROUP BY
    dt
;

-- 举例为资产池得到每个用户的所有资产
-- 使用full outer join + coalesce的写法
SELECT
    COALESCE(tt1.cst_id, tt2.cst_id) as cst_id
    ,COALESCE(tt1.bal_init_prin, 0) AS bal_init_prin
    ,COALESCE(tt1.amt_retail_prin, 0) AS amt_retail_prin
    ,COALESCE(tt2.amt_buy_prin, 0) AS amt_buy_prin
FROM    (
    SELECT
        COALESCE(t1.cst_id, t2.cst_id) as cst_id
        ,COALESCE(t1.bal_init_prin, 0) AS bal_init_prin
        ,COALESCE(t2.amt_retail_prin, 0) AS amt_retail_prin
    FROM    @bal_init t1           -- 日初资产
    FULL OUTER JOIN @amt_retail t2 -- 当天放款资产
    ON t1.cst_id = t2.cst_id
)tt1
FULL OUTER JOIN @amt_buy tt2       -- 当天买入资产
ON tt1.cst_id = tt2.cst_id
;

-- 写法一
SELECT
    cst_id
    ,SUM(bal_init_prin) as bal_init_prin
    ,SUM(amt_retail_prin) as amt_retail_prin
    ,SUM(amt_buy_prin) as amt_buy_prin
FROM (
    SELECT cst_id, bal_init_prin, 0 AS amt_retail_prin, 0 AS amt_buy_prin
    FROM @bal_init     -- 日初资产
    union ALL
    SELECT cst_id, 0 AS bal_init_prin, amt_retail_prin, 0 AS amt_buy_prin
    FROM @amt_retail   -- 当天放款资产
    UNION ALL
    SELECT cst_id, 0 AS bal_init_prin, 0 AS amt_retail_prin, amt_buy_prin
    FROM @amt_buy      -- 当天买入资产
)t1
GROUP BY
    cst_id
;
-- 优化写法二
SELECT
    cst_id
    ,SUM(IF(flag = 1, prin, 0)) as bal_init_prin
    ,SUM(IF(flag = 2, prin, 0)) as amt_retail_prin
    ,SUM(IF(flag = 3, prin, 0)) as amt_buy_prin
FROM (
    SELECT cst_id, bal_init_prin AS prin, 1 AS flag
    FROM @bal_init    -- 日初资产
    union ALL
    SELECT cst_id, amt_retail_prin AS prin, 2 AS flag
    FROM @amt_retail  -- 当天放款资产
    UNION ALL
    SELECT cst_id, amt_buy_prin AS prin, 3 AS flag
    FROM @amt_buy    -- 当天买入资产
)t1
GROUP BY
    cst_id
;