需求和背景:不能直接對(duì)外展示具體的值,讓客戶自己查詢同比,所以需要我們直接給同比值
表頭:出行時(shí)間(天),到達(dá)國(guó)家,到達(dá)省份,到達(dá)城市,景區(qū)
解決:
我們的報(bào)表系統(tǒng)無(wú)法直接求取同比值,想到使用自定義SQL,但是要支持選擇時(shí)間,我要暴露這個(gè)字段,無(wú)法實(shí)現(xiàn),只能做表了
其實(shí)很簡(jiǎn)單,我需要在select同時(shí)暴露當(dāng)期值和同期值即可,我的做法是采用兩段子句join ,在join的條件上限制
concat(CAST(CAST(substring(a.d, 1, 4) AS int) - 1 AS string), substring(a.d, 5, 7)) = b.d
但是這樣寫,查詢的結(jié)果與使用底表查結(jié)果數(shù)據(jù)不一致,后來(lái)發(fā)現(xiàn)使用了錯(cuò)誤的join,如果左邊的數(shù)據(jù)為空,就不會(huì)有右邊的數(shù)據(jù)了,這樣查詢結(jié)果就不一致了,所有應(yīng)該使用 full join ,但是又有問(wèn)題,我們查詢的時(shí)候會(huì)限制數(shù)據(jù)不為null,所以在select 上需要作出一層判斷:如果a的國(guó)家/省份/城市/景點(diǎn)為空,則為b
大致如下:
SELECT if(a.d IS NULL, concat(CAST(CAST(substring(b.d, 1, 4) AS int) + 1 AS string), substring(b.d, 5, 7)), a.d)
, '中國(guó)' AS country
, if(a.prov IS NULL, b.prov , a.prov )
, if(a.city IS NULL, b.city , a.city )
, if(a.name IS NULL, b.name , a.name )
, a.persons AS current_persons, b.persons AS pre_persons, a.order_prices AS current_prices, b.order_prices AS pre_prices
FROM (
SELECT d, prov , city , name
, SUM(person_num) AS persons, SUM(order_price) AS order_prices
FROM tabel
WHERE d >= '2019-01-01'
AND country= '中國(guó)'
AND prov IS NOT NULL
AND city IS NOT NULL
AND name IS NOT NULL
GROUP BY d, prov , city ,name
) a
full JOIN (
SELECT d, prov , city , name
, SUM(person_num) AS persons, SUM(order_price) AS order_prices
FROM tabel
WHERE d >= '2019-01-01'
AND country= '中國(guó)'
AND prov IS NOT NULL
AND city IS NOT NULL
AND name IS NOT NULL
GROUP BY d, prov , city , name
) b
ON a.prov = b.prov
AND a.city = b.city
AND a.name = b.name
AND concat(CAST(CAST(substring(a.d, 1, 4) AS int) - 1 AS string), substring(a.d, 5, 7)) = b.d
環(huán)比同理在join上改變條件即可
