2024/1/7 本文在之前发布时，最后的案例选择了错误的方法，从公众号转到博客过程中，做了修订。 ——喜乐君
喜乐君按：我的一家关键客户之前多次问过我如何在tableau中生成“日历表”，比如2022年的全年日期数据，从而和车间的生产数据匹配，我表示只能借助手工创建成数据表（毕竟几年也不过千行）。
今天阅读leaning SQL 第10章中，惊奇地发现作者Alan Beaulieu用 cross join生成笛卡尔积，巧妙地解决了这个问题——虽然tableau不能无中生有创建明细表，但是SQL却可以借助select间接完成。因此，使用tableau中的自定义SQL，就可以生成日历表了。
⚠️ 本文适用于高级用户，SQL用mysql 8完成
Feb 3, 2022 喜乐君
📌 核心问题：如何在 Tableau 中生成完整的日历表，以填补业务数据中的日期空缺
📌 解决思路：借助 SQL CROSS JOIN 生成笛卡尔积，配合 DATE_ADD 函数创建任意年份的连续日期序列
📌 业务应用：将日历表与销售数据关联，实现歇业日期的零值填充与客户流失分析
1- Cross Joins 交叉连接 生成日历表
cross joins的目的在于生成笛卡尔积，虽然没有on条件的join默认是笛卡尔积，不过建议使用cross join的连接方式。
// from 网络
接下来，才是最重要的内容——如何使用union all和cross join的功能，创建2018年的日历。
书中先构建了一个从0到399的序列，fabricate有创造、伪造、虚构之意，fabricated table即本来不存在，因分析需要而构建的数据表。
我先用一个简单的练习，比如set{0,1,2,3,4} 和 set {10,11} 做cross join，如下
mysql> -- cross join同理，要完成0～99的 data set，只可以通过0～9的数据set 和0、10、20……90的set计算笛卡尔积完成。
m = {0,1,2,3,4,5,6,7,8,9}n= {0,10,20,30,40,50,60,70,80,90}
通过sql的cross join，如下生成，为了便于理解，我增加了order by clause，通过二者的和排序，即0～99的序列：
mysql> -- cross join而为了完成0～399的序列，就可以再增加一个select clause，增加一个{0,100,200,300}和上面的结果计算笛卡尔积，最高可以获得99+300 =399的数字。
-- m = {0,1,2,3,4,5,6,7,8,9}cross join 用于生成 0～399的data set数据集，而date_add函数用于把数据集转化为日期，如下所示：
mysql>如果只想保留2022年的日期，也可以在上述的SQL最后，增加一个where条件如下：
select "2022-01-01",2- 销售数据与日历表的结合应用
Now that you have a mechanism for fabricating all the days in 2008, what shou you do with it ? well , you might be asked to generate a query that shows every day in 2008 along with the number of banking transactions couducted on that day, the number of accounts opened on that day, and so forth.
比如我用超市的数据来看，2018年，并非每一天都有营业，此时不管是离散的日期，还是连续的日期，都不能得到每一天的数据——数据表中压根没有“闭门歇业”期间的任何数据。比如在tableau中摘取一段如下所示：
【补充2022】
上面的问题，其实不需要用join创建数据。如果只是为了显示0，Tableau Desktop可以借助于格式设置完成。
3、使用数据合并在明细行中补充数据
如果我们需要标记的不是0，而是其他逻辑。比如没有购买标记“客户流程”，然后分析每一天“前一天购买，当日流失的客户比例”，这样就需要在数据明细中明确的增加一行，从而完成后续判断。
喜乐君之前写过一篇Prep文章，旨在在数据准备阶段完成此类分析。 【深度Tableau】Prep Builder2021.3“新行”与客户迁徙数据准备案例 如果数量比较少，追求简单灵活，也可以在视图中使用合并完成。
比如，可以把已有的数据，和日历表做一个关联，从而生成每一天的数据，这里我先使用tableau desktop完成这个简易过程。
这样，即便没有营业的日期，也至少有一行明细数据，只是对应的字段都是空值，此时的柱状图或者折线图，都会出现歇业日期的业绩——0.
当然，书中自然是用SQL完成这里的过程，我可以将超市数据和日历表合并如下
-- superstore right outer join calendar day
Select cast(sp.订单日期 AS DATE) sp订单日期, cast(calender.dt AS DATE) dt , count(distinct sp.`订单 Id`) 订单数
from tableau.superstore sp
right outer join
(
select -- "2018-01-01",
-- one.num ,two.num,three.num,
one.num+two.num+three.num as total,
cast(date_add( '2018-01-01', interval one.num+two.num+three.num Day) as DATE) dt
from
(
(select 0 num union all
select 1 num union all
select 2 num union all
select 3 num union all
select 4 num union all
select 5 num union all
select 6 num union all
select 7 num union all
select 8 num union all
select 9 num ) one
cross join
(select 0 num union all
select 10 num union all
select 20 num union all
select 30 num union all
select 40 num union all
select 50 num union all
select 60 num union all
select 70 num union all
select 80 num union all
select 90) two
cross join
(select 0 num union all
select 100 num union all
select 200 num union all
select 300) three )
where date_add( '2018-01-01', interval one.num+two.num+three.num Day) <='2018-12-31' -- filter to year 2022
-- order by total
) calender
on cast(sp.订单日期 AS DATE) = cast(calender.dt AS DATE)
group by cast(sp.订单日期 AS DATE), cast(calender.dt AS DATE)
order by 2;
书中，作者也兴奋地说，
“This is one of the more interesting queries thus far in the book, in that it includes cross joins, outer joins, a data function, grouping, set operations(union all), and an aggregation . It is also not the most elegant solution to the given problem, but it should server as an example of how, with a little creativity and a firm grasp on the language, you can make even a seldom-used feature like cross joins a potent tool in your SQL toolkit”
- 🌐 XILEJUN.com 全球 → https://xilejun.com
- 🌐 VIZWISE.cn 国内 → https://vizwise.cn
- 【深度Tableau】Prep Builder2021.3“新行”与客户迁徙数据准备案例 → https://xilejun.com/dwbi/etl_prep/prep-builder-new-rows/
No comments yet