flink sql实现interval join的图解

爱被打了一巴掌 2022-10-24 13:30 312阅读 0赞

# 完整SQL代码 #

[https://gitee.com/appleyuchi/Flink\_Code/blob/master/flink-sql-cookbook/SQL/Interval\_Joins.sql][https_gitee.com_appleyuchi_Flink_Code_blob_master_flink-sql-cookbook_SQL_Interval_Joins.sql]

# 基本概念 #

TIMESTAMPDIFF的作用

mysql> select TIMESTAMPDIFF(DAY, '2018-03-20 23:59:00', '2015-03-22 00:00:00');
    +------------------------------------------------------------------+
    | TIMESTAMPDIFF(DAY, '2018-03-20 23:59:00', '2015-03-22 00:00:00') |
    +------------------------------------------------------------------+
    |                                                            -1094 |
    +------------------------------------------------------------------+

意思是:

2015年的某天-2018年的某天＝天数差是-1094天

另外，上述SQL中涉及的订单表和发货表都是数据更新很快的表格．

所以这两张表都属于事实表．

此例属于两个事实表进行Join操作．

# Interval Join查询语句 #

SELECT
      o.id AS order_id,
      o.order_time,
      s.shipment_time,
      TIMESTAMPDIFF(DAY,o.order_time,s.shipment_time) AS day_diff
    FROM orders o
    JOIN shipments s ON o.id = s.order_id
    WHERE 
        o.order_time BETWEEN s.shipment_time - INTERVAL '3' DAY AND s.shipment_time;

**上述SQL语句的含义是:**

<table> 
 <tbody> 
  <tr> 
   <td style="width:588px;"><strong>SQL语句部分</strong></td> 
   <td style="width:359px;"><strong>业务含义</strong></td> 
  </tr> 
  <tr> 
   <td style="width:588px;">o.id AS order_id</td> 
   <td style="width:359px;">订单ID</td> 
  </tr> 
  <tr> 
   <td style="width:588px;">o.order_time</td> 
   <td style="width:359px;">下单时间</td> 
  </tr> 
  <tr> 
   <td style="width:588px;">s.shipment_time</td> 
   <td style="width:359px;">发货时间</td> 
  </tr> 
  <tr> 
   <td style="width:588px;">TIMESTAMPDIFF(DAY,o.order_time,s.shipment_time)</td> 
   <td style="width:359px;">发货时间与下单时间的差</td> 
  </tr> 
  <tr> 
   <td style="width:588px;">FROM orders o JOIN shipments s ON o.id = s.order_id</td> 
   <td style="width:359px;"> <p>两张表(orders与shipments)中下单ID等于发货ID的数据</p> </td> 
  </tr> 
  <tr> 
   <td style="width:588px;">o.order_time BETWEEN s.shipment_time - INTERVAL '3' DAY AND s.shipment_time;</td> 
   <td style="width:359px;">计算下单后3天内发货的订单</td> 
  </tr> 
 </tbody> 
</table>

上述最后一部分的图解如下:

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FwcGxleXVjaGk_size_16_color_FFFFFF_t_70][]

**上面的图被误伤，下面的图是重新画的。**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FwcGxleXVjaGk_size_16_color_FFFFFF_t_70 1][]

这个图的意思是：

查询下单后3天内发货的订单。

[https_gitee.com_appleyuchi_Flink_Code_blob_master_flink-sql-cookbook_SQL_Interval_Joins.sql]: https://gitee.com/appleyuchi/Flink_Code/blob/master/flink-sql-cookbook/SQL/Interval_Joins.sql
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FwcGxleXVjaGk_size_16_color_FFFFFF_t_70]: /images/20221024/bcf68b17f23344b984b5125b8536dbd0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2FwcGxleXVjaGk_size_16_color_FFFFFF_t_70 1]: /images/20221024/36d04e1e0aff408f8c2b30b81cd8cde0.png