mysql/hive：使用窗口或分析函数连接条件行

v7pvogib 于 2021-06-28 发布在 Hive

关注(0)|答案(1)|浏览(408)

我有两个表，我想加入一个特定的逻辑。
表1（序号、id、日期1、日期2）

S_No    ID  Date1   Date2
1   id1 2014-05-01  2014-07-03
2   id1 2015-03-23  2016-06-18
3   id1 2016-06-21  2016-07-29

表2（序号、id、日期）

S_No_New    ID_New  Date_New
2_1 id1 2014-04-25
2_2 id1 2014-06-14
2_3 id1 2015-01-10
2_4 id1 2015-02-15
2_5 id1 2015-05-17
2_6 id1 2016-04-24
2_7 id1 2016-06-19
2_8 id1 2016-06-25
2_9 id1 2016-07-11
2_10    id1 2016-08-11
2_11    id1 2016-08-16

我想以这样一种方式连接上面的两个表，即在date1之前以及date1和date2之间，我得到表2中有多少行的计数，然后当我们移动到下一行时，我们必须使用到目前为止还没有为同一id计数的数据。
如果表2中有一个date条目在表1中最后一个date2条目之后，那么我们需要添加一个新行，并将“+1”添加到suno和类似的剩余列细节中。
总的来说，这个问题可以分为两部分：
1）获取计数列
2）把多余的行加起来（本例中序号为“4”）
如果你知道解决这两个问题的方法，请不要回答。
最终输出：

S_No    ID  Date1   Date2   Count_pre   Count_Between
1   id1 2014-05-01  2014-07-03  1   1
2   id1 2015-03-23  2016-06-18  2   2
3   id1 2016-06-21  2016-07-29  1   2
4   id1 NULL    NULL    2   0

逻辑：
序号1：
count_pre=2014-05-01之前的日期
count_between=日期介于2014-05-01和2014-07-03之间
序号2：
count_pre=日期介于2014-07-03和2015-03-23之间
count_between=日期介于2015-03-23和2016-06-18之间
等等
中间表必须如下所示：

S_No    ID  Date    Date2   S_No_New    Date_New
1   id1 2014-05-01  2014-07-03  2_1 2014-04-25
1   id1 2014-05-01  2014-07-03  2_2 2014-06-14
2   id1 2015-03-23  2016-06-18  2_3 2015-01-10
2   id1 2015-03-23  2016-06-18  2_4 2015-02-15
2   id1 2015-03-23  2016-06-18  2_5 2015-05-17
2   id1 2015-03-23  2016-06-18  2_6 2016-04-24
3   id1 2016-06-21  2016-07-29  2_7 2016-06-19
3   id1 2016-06-21  2016-07-29  2_8 2016-06-25
3   id1 2016-06-21  2016-07-29  2_9 2016-07-11
4   id1 NULL    NULL    2_10    2016-08-11
4   id1 NULL    NULL    2_11    2016-08-16

我尝试了不同的窗口和分析功能，但无法通过这个问题。是否可以在配置单元（基本sql）中执行这种连接？
注意：edit2：我需要在hive中实现它，它支持所有内置函数，但不支持mysql中的变量。它支持聚合、窗口和分析功能。
编辑：将日期格式从dd/mm/yyyy更改为yyyy-mm-dd

mysql Hive window-functions aggregate-functions analytic-functions

来源：https://stackoverflow.com/questions/40123997/mysql-hive-join-conditioned-rows-using-windowing-or-analytical-functions

1条答案

按热度按时间

ljo96ir51#

SELECT t.t1s_no,t.date1,t.date2,
         sum(case when t.srce = 'P' then 1 else 0 end) as 'prev',
         sum(case when t.srce = 'B' then 1 else 0 end) as 'between',
         sum(case when t.srce = 'X' then 1 else 0 end) as 'missing'
FROM 
(
SELECT S.*,
        ROW_NUMBER() OVER (PARTITION BY S.DATE_NEW ORDER BY s.srce ,S.DATE1) RN
FROM
(SELECT 'P' AS SRCE,T1.S_NO T1S_NO,T1.ID T1ID,T1.DATE1 DATE1,T1.DATE2 DATE2,T2.DATE_NEW
FROM    TABLE_1 T1
JOIN    TABLE_2 T2 ON T2.DATE_NEW < T1.DATE1
UNION 
SELECT 'B' AS SRCE,T1.S_NO T1S_NO,T1.ID T1ID,T1.DATE1 DATE1,T1.DATE2 DATE2,T2.DATE_NEW
FROM    TABLE_1 T1
JOIN    TABLE_2 T2 ON T2.DATE_NEW BETWEEN T1.DATE1 AND T1.DATE2
UNION 
SELECT 'X' AS SRCE,4 T1S_NO,T1.ID T1ID,T1.DATE1 DATE1,T1.DATE2 DATE2,T2.DATE_NEW
FROM    TABLE_2 T2
left JOIN   TABLE_1 T1 ON (T2.DATE_NEW BETWEEN T1.DATE1 AND T1.DATE2) or (t2.date_new < t1.date1) 
where   t1.date1 is null
) S 
) T
WHERE T.RN = 1 
group   by t.t1s_no,t.date1,t.date2
ORDER BY T.T1S_NO, T.DATE1
;

结果

t1s_no      date1            date2            prev        between     missing
----------- ---------------- ---------------- ----------- ----------- -----------
          1       2014-05-01       2014-07-03           1           1           0
          2       2015-03-23       2016-06-18           2           2           0
          3       2016-06-21       2016-07-29           1           2           0
          4             NULL             NULL           0           0           2

(4 rows affected)

赞(0）回复(0）举报 2021-06-28

我来回答

mysql/hive：使用窗口或分析函数连接条件行

1条答案

相关问题

热门标签

最新问答