需求和背景:不能直接对外展示具体的值,让客户自己查询同比,所以需要我们直接给同比值
表头:出行时间(天),到达国家,到达省份,到达城市,景区
解决:
我们的报表系统无法直接求取同比值,想到使用自定义SQL,但是要支持选择时间,我要暴露这个字段,无法实现,只能做表了
其实很简单,我需要在select同时暴露当期值和同期值即可,我的做法是采用两段子句join ,在join的条件上限制
concat(CAST(CAST(substring(a.d, 1, 4) AS int) - 1 AS string), substring(a.d, 5, 7)) = b.d
但是这样写,查询的结果与使用底表查结果数据不一致,后来发现使用了错误的join,如果左边的数据为空,就不会有右边的数据了,这样查询结果就不一致了,所有应该使用 full join ,但是又有问题,我们查询的时候会限制数据不为null,所以在select 上需要作出一层判断:如果a的国家/省份/城市/景点为空,则为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)
, '中国' 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= '中国'
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= '中国'
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
环比同理在join上改变条件即可
