解锁SQL无限可能 | 如何利用SQL解决趣味赛马问题？| 非等值关联匹配问题

目录

0 问题描述

1 数据准备

2 问题分析

方法一：先分后合思想

方法2：非等值关联匹配

3 小结

---

0 问题描述

有一张赛马记录表，如下所示：

 create table RacingResults (	
 trace_id char(3) not null,
 race_date date not null,	
 race_nbr int not null,
 win_name char(30) not null,	
 place_name char(30) not null,	
 show_name char(30) not null,	
 primary key (trace_id, race_date, race_nbr)
 ) type=InnoDB default charset="UTF8";

 

其中：trace_id列 是举行比赛的赛道的名称,

           race_date 是举行比赛的日期,

           race_nbr是每次比赛的编号,
           另外3列是在比赛获得第一、第二和第三名的马的名字：

          win表示马是第一名.

          place表示马是第一或第二名.

          show表示马是第一、第二或第三名. 

需求：取得每匹马获奖的次数 

1 数据准备

create table RacingResults as
    (
    select stack(
                    7
                , "001", "2008-12-25", 1, "horce1", "horce2", "horce3"
                , "002", "2008-12-25", 1, "horce2", "horce3", "horce4"
                , "003", "2008-12-25", 1, "horce3", "horce4", "horce5"
                , "004", "2008-12-25", 1, "horce4", "horce5", "horce6"
                , "005", "2008-12-25", 1, "horce5", "horce6", "horce7"
                , "006", "2008-12-25", 1, "horce6", "horce7", "horce8"
                , "007", "2008-12-25", 1, "horce7", "horce8", "horce9"
    ) as (trace_id, race_date, race_nbr, win_name, place_name, show_name)
    );

2 问题分析

问题要求的是每匹马获奖的次数，可以看出只要马获奖即可，并不关心马是前三名中的哪一个名次。

方法一：先分后合思想

分别算出每种获奖名词类型下的每匹马的获奖次数，最终将每匹马的获奖次数相加即可

步骤1：行转列，分别计算每种获奖类型下每匹马的获奖次数

select race_date, race_nbr, win_name house, 'win_name' type, count(*) race_cnt
from racingresults
group by race_date, race_nbr, win_name
union all
select race_date, race_nbr, place_name house, 'place_name' type, count(*) race_cnt
from racingresults
group by race_date, race_nbr, place_name
union all
select race_date, race_nbr, show_name house, 'show_name' type, count(*) race_cnt
from racingresults
group by race_date, race_nbr, show_name

计算结果明细表如下：

步骤2：基于步骤1，计算每匹马获奖次数。

最终的SQL如下：

select race_date, race_nbr, house, sum(race_cnt) race_cnt
from (select race_date, race_nbr, win_name house, 'win_name' type, count(*) race_cnt
      from racingresults
      group by race_date, race_nbr, win_name
      union all
      select race_date, race_nbr, place_name house, 'place_name' type, count(*) race_cnt
      from racingresults
      group by race_date, race_nbr, place_name
      union all
      select race_date, race_nbr, show_name house, 'show_name' type, count(*) race_cnt
      from racingresults
      group by race_date, race_nbr, show_name) t
group by race_date, race_nbr, house

 

方法2：非等值关联匹配

计算维度表dim:马表，匹配数据表求解（数仓常用思路）

第一步：计算马表 维度表

select win_name house
      from racingresults
      union
      select place_name house
      from racingresults
      union
      select show_name house
      from racingresults

第二步：用马表维度表关联数据表，进行非等值匹配

select race_date
     , race_nbr
     , house
     , trace_id
     , win_name
     , place_name
     , show_name
from (select win_name house
      from racingresults
      union
      select place_name house
      from racingresults
      union
      select show_name house
      from racingresults
      ) dim
         left join racingresults data
                   on dim.house = data.win_name or dim.house = data.place_name or dim.house = data.show_name

 

步骤3：基于步骤2明细表进行汇总计算，计算每匹马的获奖次数

最终SQL如下：

 

select race_date
     , race_nbr
     , house
     , count(*) win_cnt
from (select win_name house
      from racingresults
      union
      select place_name house
      from racingresults
      union
      select show_name house
      from racingresults) dim
         left join racingresults data
                   on dim.house = data.win_name or dim.house = data.place_name or dim.house = data.show_name
group by race_date
       , race_nbr
       , house

 

3 小结

本文通过趣味问题“赛马”问题来分析如何解决非等值关联的匹配问题。这类问题的一般处理思路有两种：一种是利用先分后合的思想，先利用行转列，UNION 的方式来分别处理各自类型的数据，最终在合并汇总；一种是利用构建维度表（如本题中的马表）关联数据表的形式进行非等值匹配，这种构建维度表再关联数据表的形式在数仓开发中非常常见，两种方法都很巧妙均需要掌握。

~~END~~

如果觉得本篇文章对你还有点帮助，那么不妨也可以选择去看看我的博客专栏文章，里面内容更精彩。具体专栏内容如下（部分展示）：

数字化建设通关指南专栏原价99，现在活动价29.9，按照阶梯式增长，直到恢复原价
主要内容包括：

1 SQL进阶实战技巧

可以参考如下教程，具体链接如下

SQL很简单，可你却写不好？也许这才是SQL最好的教程

上面链接中的文章及技巧会不定期更新。

2 数仓建模实战技巧和个人心得

文章部分标题展示如下：
       1）新人入职新公司后应如何快速了解业务？

       2）以业务视角看宽表化建设？

       3)  维度建模 or 关系型建模？

       4）业务模型与数据模型有什么区别？业务阶段的模型该如何建设？

       5）业务指标体系该如何建设？指标体系该如何维护？指标平台应如何建设？指标体系                           该由谁来搭建？

       6）如何优雅设计DWS层？DWS层模型好坏该如何评价？

       7）指标发生异常，该如何排查？应从哪些方面入手寻找问题点？

       8） 数据架构的选择，mpp or hadoop?

       9）数仓团队应如何体现自己的业务价值，讲好数据故事？

       10）BI与大数据有什么关系？BI与信息化、数字化之间有什么关系？BI与报表之间的关                          系？

       11）数据部门如何与业务部门沟通，并规划指引业务需求？

文章不限于以上内容，有新的想法也会及时更新到该专栏。

具体专栏链接如下：

数字化建设通关指南_莫叫石榴姐的博客-CSDN博客