tableau sql心得

最近在写一个tableau的页面，遇到一个问题，零售值与目标值关联查询起来。零售的目标值总是对不上。

最开始才用的是左连接

SELECT
	'零售' vvflag,
	m.big_name,
	m.province,
	m.series_name,
	CONCAT(m.period_month,'-01') yymonth,
	sum(sell_num) sales_counts, --零售值
	max(tt.retail_month) target_count  -- 目标值
FROM
	`dmr`.`dmr_sal_sales_count` m
LEFT JOIN (
SELECT
t.big_name  as big_name, -- 大区
t.province  as province, -- 省份
t.group_name as series_name,-- 车系
concat(t.plan_year,'-',lpad(t.plan_month,2,'0')) as yymonth,
SUM(t.retail_month)  as retail_month -- 零售
FROM
dmk.dmk_sal_tm_retail_wholesale_target t
where t.vllj_flag = 'N'
group by concat(t.plan_year,'-',lpad(t.plan_month,2,'0')),t.big_name,t.province,t.group_name
) tt ON m.big_name = tt.big_name
AND m.province = tt.province
AND m.series_name = tt.series_name
AND m.period_month = tt.yymonth

1. 因为在实际业务中，可能零售没有卖出去东西就没有这条数据，也可能车系目标值没有定目标也没这条数据，所以左连接放任何一张表都可能会丢失数据。

解决方法如下

SELECT 
  '零售' as vvflag,t1.big_name,t1.province,t1.series_name,t1.yymonth,SUM(t1.sales_counts) sales_counts ,SUM(t1.target_count) target_count
from 
(SELECT
	m.big_name,-- 大区
	m.province,-- 省份
	m.series_name,-- 车系
	CONCAT(m.period_month,'-01') yymonth,
	sum(sell_num) sales_counts,
	0 target_count
FROM
	`dmr`.`dmr_sal_sales_count` m
WHERE
	m.vllj_flag <> 'Y'
GROUP BY
	m.big_name,
	m.province,
	m.series_name,
	m.period_month
UNION ALL
 SELECT
t.big_name  as big_name, -- 大区
t.province  as province, -- 省份
t.group_name as series_name,-- 车系
CONCAT(concat(t.plan_year,'-',lpad(t.plan_month,2,'0')),'-01') as yymonth,
 0          as  sales_counts,-- 零售
SUM(t.retail_month)  as target_count -- 零售
FROM
dmk.dmk_sal_tm_retail_wholesale_target t
where t.vllj_flag = 'N'
group by concat(t.plan_year,'-',lpad(t.plan_month,2,'0')),t.big_name,t.province,t.group_name)t1
group by t1.big_name,t1.province,t1.series_name,t1.yymonth

先分别查出零售值与目标值 然后用 UNION ALL函数把他们合并起来最后再最外面group by。这样就解决了目标和零售都可能为空的情况解决方法