巧用CTE结果集完成数据抓取

  场景：扫码购，近期活动，回馈广大会员，给VIP顾客一个大大的惊喜:以每2个小时为一个时段，筛选出各时段内的现金支付最高的大单交易，活动内容很简单，而对于IT来说就一个要求：及时准确地提供数据。

  首先就来分析下这个数据。要完成这项工作，信息人员需要交易数据、类现金支付的支付数据，还有一个就是交易的VIP 数据。这些数据抓取都很简单，利用多表join起来是可以的，但是多表的join在我看来数据库的SQL语句代码是比较累赘的，看起来也不够美观优雅。所以我这里给大家推荐另外一种实现方式：CTE。

  什么是CTE呢？CTE是公用表表达式，英文为Common Table Expression,你可以把它看作是一个临时的结果集。我们讲上述需要的各种数据，分别作为一个个的结果集。在需要的时候，直接拿出来对这些结果集进行处理，就好比临时一样。使用CTE的最大好处就是可以让语句更加清晰简练，也可以说代码比较优雅。

 那CTE的定义是什么呢？CTE的定义语法如下，主要包括3个部分：

(1) Expression_name：CTE表达式的名称

(2) Column_name：列名列表

(3) CTE_query_defnition：定义CTE结果集的Select查询语句

格式如下：

 

WITH  expression_name  [(column_name [,...n] )]

 

AS

(

 

cte_query_defnition

 

)

 

以上面的需求为例，我们使用 CET来实现抓取2020年七夕的数据。首先我们需要交易数据。交易数据可以去销售主表抓取，保留交易号，交易标志，交易金额，交易的卡号等

  根据CTE我们可以讲上述的Select语句作为CTE结果集来使用。那怎么修改呢？

对于支付数据，我们也可以讲起作为CTE的结果集。对于支付需要重点指出的是，我们要抓取那些类现金的支付方式。所以需要对支付方式做个限制，可以通过bas_payment_mode.action_fag标志来进行限定；凡是活动标志action_fag为1，即代表支付方式是参与活动，便可认为是类现金的支付方式。具体CET语句如下：

 

 

　上述需求所要的数据还有一个比较重要的是 VIP数据。那么所抓取的数据同样可以作为 CTE 的结果集来处理。

 

 

 

所需的数据结果集已经全部抓取了，接下来就是根据需求，对结果集进行处理。怎么办呢？将多个结果集结合起来进行数据的操作。如下图语句所示：

 

我们来分析一下上述语句。要知道，交易里可以使用多支付方式，甚至只要顾客有相应不限收且足额的电子券，都可以不用类现金的支付就可完成交易；如果不需要积分的话,交易也可以不刷VIP卡。如果想要抓取这些特例情况下的数据就需要使用左外连接的方式来进行查询，即将左侧exch_masterr数据集的结果全部打印出来，即使是右侧数据为空也一样。在这里的意思就是说：即使不是类现金支付的交易，也要将交易数据打印出来。同理，左外连接也会将未刷 VIP卡的交易数据也全部打印出来。左外连接实现方式如下图所示：

这样一来，你会发现，有很多的不是类现金支付的交易也查出来了， cash_cons_sum 为 Null 。降序排列后，发现Null总是排在前面，这会影响我们查看现金消费(cash_cons_sum)，最高的那一行。怎么办呢？使用 nulls last 将 cash_cons_sum为Null 的行往后放。

这样就 OK了吗？仔细想一想，我们是要每两个小时取一次数据。再看看时间段的设置.

 

都是类似202008251100000这样长字符串的文本，每次改起来是不是很麻烦。所以要想个办法来处理下。最好是在查询时只需输入一个数字代表整点即可；比如在中午 12点多时想查询10 点至 12 之间的数据，我们只要输入 10 。完整的语句如下:

 

请重点注意红框中的语句。如此一来，在确定日期的前提下，我们只需要修改 fromTime 变量所代表的整点时间数字即可