Impala SQL：合并具有重叠日期的行。不支持WHERE EXISTS和递归CTE
技术问答
304 人阅读
|
0 人回复
|
2023-09-13
|
|
我正在尝试在Impala SQL的表中合并具有重叠日期间隔的行。但是，Impala不支持我找到的解决方案。存在和递归CTE。
: v: m7 v( _3 y+ }9 }. D! N7 ]我将如何在Impala中为此编写查询？
- D1 F1 O: j$ `" Q3 f8 q Table: @T7 ]6 t9 Y: C! s) n# f. x' }
ID StartDate EndDate
% ?( D* D( H3 x# H3 L 1 20170101 20170201! G- i+ h2 }9 |; X* b( {
2 20170101 20170401; k- K1 q" Y1 c- I% s$ T: h# U
3 20170505 20170531 2 M0 j! |5 b j
4 20170530 20170531
- A8 S0 R& Z7 y) ~7 @: s( V7 N' {9 t 5 20170530 20170831 @+ Q+ Q& ~' l. t7 P0 K) Y- h
6 20171001 201710054 k# W5 I2 H5 x: V! `, ~
7 20171101 20171225
8 V4 m3 ]2 L. u5 E4 g 8 20171105 20171110
' h' c) L l/ c: P9 h( g- l Required Output:
" g* V3 w4 T3 o G$ h' t StartDate EndDate
$ ]5 p" k% s4 h6 A! {9 t, C 20170101 201704012 @+ \5 D/ f1 l2 U
20170505 20170831- b0 a% R, N% S1 u; O, A
20171001 20171005
) }5 ]7 m# Q1 R# W) x7 k- m6 XImpala不支持我尝试实现的示例：
% A& i! A: x4 o2 s SELECT
, h( D5 T/ J7 u1 V! D s1.StartDate,
( B/ Q# h3 t+ A) c+ C- {3 g* @ MIN(t1.EndDate) AS EndDate
/ y) f' B& I6 y+ T- Q, _& z FROM @T s1
" C" h& B6 S4 p' Q( S4 J* S5 W INNER JOIN @T t1 ON s1.StartDate = t2.StartDate AND t1.EndDate s2.StartDate AND s1.StartDate select min(StartDate) as StartDate
6 Z( P; L* k/ P3 W6 e ,max(EndDate) as EndDate- \* Z$ ]% }; }. T. m+ j2 `1 K2 I& k$ Z' O
from (select StartDate,EndDate
8 w# J/ o8 p( ^4 w% O! L( @- m ,count (is_gap) over7 c9 g k% x: Z3 a
(
W; a+ n! a' G: R5 U order by StartDate,ID6 T) x# L. w& K$ {+ U! i+ n, I8 X
) as range_id; l4 B# A4 ^! ~% ~; p0 m
from (select ID,StartDate,EndDate
5 P* [, [+ v9 I0 M ,case
9 A. C, X2 P# j* e4 s: W( E when max (EndDate) over
" Z# e& R6 x- T8 q2 }& ~( b8 ~: a% M( l* d (0 V* u* |; Y: U$ v# X; `3 }6 G" l
order by StartDate,ID
) P9 q5 {% q$ {# c# t* \# ? rows between unbounded preceding
2 K) {1 c, `. i) c and 1 preceding
. \4 \ h& m1 [8 o2 b. d )
1 W+ o" y+ f5 d+------------+------------+: r7 e+ x7 e. P7 `( q; p
| startdate | enddate |
" O5 j8 Z: t( q9 ], g+------------+------------+( h6 ^, f$ v/ h( w8 ~5 L- b) b8 c
| 2017-01-01 | 2017-04-01 |
2 i' p: Y) Y( @9 A| 2017-05-05 | 2017-08-31 |5 j* ?: l5 E) L' M
| 2017-10-01 | 2017-10-05 |4 I9 \7 n, |$ O9 f
| 2017-11-01 | 2017-12-25 |
5 T- o4 o+ H+ Y) S- z1 Z+------------+------------+ |
|
|
|
|
|
|
|
|
|
