1 -- 根据指定日期找出前后30天内生日的客户
2 select
3 t2.day as etl_dt
4 ,t1.id
5 ,t1.day
6 ,t2.flag_first
7 ,t2.flag_last
8 ,concat(year(t1.day) - 1,t2.day_first) as day_first_1
9 ,concat(year(t1.day),t2.day_last) as day_last_1
10 ,concat(year(t1.day),t2.day_first) as day_first_2
11 ,concat(year(t1.day) + 1,t2.day_last) as day_last_2
12 ,case
13 when t2.flag_first = 1 and t1.day >= concat(year(t1.day) - 1,t2.day_first) and t1.day <= concat(year(t1.day),t2.day_last) then 1
14 when t2.flag_last = 1 and t1.day >= concat(year(t1.day),t2.day_first) and t1.day <= concat(year(t1.day) + 1,t2.day_last) then 1
15 else 0
16 end as flag
17 from (
18 select 2 as id, '1966-02-23' as day union all
19 select 1 as id, '1965-12-23' as day union all
20 select 3 as id, '1994-10-15' as day union all
21 select 4 as id, '1995-10-23' as day
22 ) t1
23 left join (
24 select
25 day
26 ,case
27 when year(date_add(day,-59)) = year(day) then 0
28 else 1
29 end as flag_first
30 ,case
31 when year(date_add(day,59)) = year(day) then 0
32 else 1
33 end as flag_last
34 ,substr(date_add(day,-59),5,6) as day_first
35 ,substr(date_add(day,59),5,6) as day_last
36 from (
37 select '2018-01-16' as day union all
38 select '2018-02-16' as day union all
39 select '2018-03-16' as day union all
40 select '2018-04-16' as day union all
41 select '2018-05-16' as day union all
42 select '2018-06-16' as day union all
43 select '2018-07-16' as day union all
44 select '2018-08-16' as day union all
45 select '2018-09-16' as day union all
46 select '2018-10-16' as day union all
47 select '2018-11-16' as day union all
48 select '2018-12-16' as day
49 ) t11
50 ) t2
51 on 1 = 1
52 order by etl_dt,t1.id
53 ;
+-------------+-----+-------------+-------------+------------+--------------+-------------+--------------+-------------+-------+--+
| etl_dt | id | day | flag_first | flag_last | day_first_1 | day_last_1 | day_first_2 | day_last_2 | flag |
+-------------+-----+-------------+-------------+------------+--------------+-------------+--------------+-------------+-------+--+
| 2018-01-16 | 1 | 1965-12-23 | 1 | 0 | 1964-11-18 | 1965-03-16 | 1965-11-18 | 1966-03-16 | 0 |
| 2018-01-16 | 2 | 1966-02-23 | 1 | 0 | 1965-11-18 | 1966-03-16 | 1966-11-18 | 1967-03-16 | 1 |
| 2018-01-16 | 3 | 1994-10-15 | 1 | 0 | 1993-11-18 | 1994-03-16 | 1994-11-18 | 1995-03-16 | 0 |
| 2018-01-16 | 4 | 1995-10-23 | 1 | 0 | 1994-11-18 | 1995-03-16 | 1995-11-18 | 1996-03-16 | 0 |
| 2018-02-16 | 1 | 1965-12-23 | 1 | 0 | 1964-12-19 | 1965-04-16 | 1965-12-19 | 1966-04-16 | 0 |
| 2018-02-16 | 2 | 1966-02-23 | 1 | 0 | 1965-12-19 | 1966-04-16 | 1966-12-19 | 1967-04-16 | 1 |
| 2018-02-16 | 3 | 1994-10-15 | 1 | 0 | 1993-12-19 | 1994-04-16 | 1994-12-19 | 1995-04-16 | 0 |
| 2018-02-16 | 4 | 1995-10-23 | 1 | 0 | 1994-12-19 | 1995-04-16 | 1995-12-19 | 1996-04-16 | 0 |
| 2018-03-16 | 1 | 1965-12-23 | 0 | 0 | 1964-01-16 | 1965-05-14 | 1965-01-16 | 1966-05-14 | 0 |
| 2018-03-16 | 2 | 1966-02-23 | 0 | 0 | 1965-01-16 | 1966-05-14 | 1966-01-16 | 1967-05-14 | 0 |
| 2018-03-16 | 3 | 1994-10-15 | 0 | 0 | 1993-01-16 | 1994-05-14 | 1994-01-16 | 1995-05-14 | 0 |
| 2018-03-16 | 4 | 1995-10-23 | 0 | 0 | 1994-01-16 | 1995-05-14 | 1995-01-16 | 1996-05-14 | 0 |
| 2018-04-16 | 1 | 1965-12-23 | 0 | 0 | 1964-02-16 | 1965-06-14 | 1965-02-16 | 1966-06-14 | 0 |
| 2018-04-16 | 2 | 1966-02-23 | 0 | 0 | 1965-02-16 | 1966-06-14 | 1966-02-16 | 1967-06-14 | 0 |
| 2018-04-16 | 3 | 1994-10-15 | 0 | 0 | 1993-02-16 | 1994-06-14 | 1994-02-16 | 1995-06-14 | 0 |
| 2018-04-16 | 4 | 1995-10-23 | 0 | 0 | 1994-02-16 | 1995-06-14 | 1995-02-16 | 1996-06-14 | 0 |
| 2018-05-16 | 1 | 1965-12-23 | 0 | 0 | 1964-03-18 | 1965-07-14 | 1965-03-18 | 1966-07-14 | 0 |
| 2018-05-16 | 2 | 1966-02-23 | 0 | 0 | 1965-03-18 | 1966-07-14 | 1966-03-18 | 1967-07-14 | 0 |
| 2018-05-16 | 3 | 1994-10-15 | 0 | 0 | 1993-03-18 | 1994-07-14 | 1994-03-18 | 1995-07-14 | 0 |
| 2018-05-16 | 4 | 1995-10-23 | 0 | 0 | 1994-03-18 | 1995-07-14 | 1995-03-18 | 1996-07-14 | 0 |
| 2018-06-16 | 1 | 1965-12-23 | 0 | 0 | 1964-04-18 | 1965-08-14 | 1965-04-18 | 1966-08-14 | 0 |
| 2018-06-16 | 2 | 1966-02-23 | 0 | 0 | 1965-04-18 | 1966-08-14 | 1966-04-18 | 1967-08-14 | 0 |
| 2018-06-16 | 3 | 1994-10-15 | 0 | 0 | 1993-04-18 | 1994-08-14 | 1994-04-18 | 1995-08-14 | 0 |
| 2018-06-16 | 4 | 1995-10-23 | 0 | 0 | 1994-04-18 | 1995-08-14 | 1995-04-18 | 1996-08-14 | 0 |
| 2018-07-16 | 1 | 1965-12-23 | 0 | 0 | 1964-05-18 | 1965-09-13 | 1965-05-18 | 1966-09-13 | 0 |
| 2018-07-16 | 2 | 1966-02-23 | 0 | 0 | 1965-05-18 | 1966-09-13 | 1966-05-18 | 1967-09-13 | 0 |
| 2018-07-16 | 3 | 1994-10-15 | 0 | 0 | 1993-05-18 | 1994-09-13 | 1994-05-18 | 1995-09-13 | 0 |
| 2018-07-16 | 4 | 1995-10-23 | 0 | 0 | 1994-05-18 | 1995-09-13 | 1995-05-18 | 1996-09-13 | 0 |
| 2018-08-16 | 1 | 1965-12-23 | 0 | 0 | 1964-06-18 | 1965-10-14 | 1965-06-18 | 1966-10-14 | 0 |
| 2018-08-16 | 2 | 1966-02-23 | 0 | 0 | 1965-06-18 | 1966-10-14 | 1966-06-18 | 1967-10-14 | 0 |
| 2018-08-16 | 3 | 1994-10-15 | 0 | 0 | 1993-06-18 | 1994-10-14 | 1994-06-18 | 1995-10-14 | 0 |
| 2018-08-16 | 4 | 1995-10-23 | 0 | 0 | 1994-06-18 | 1995-10-14 | 1995-06-18 | 1996-10-14 | 0 |
| 2018-09-16 | 1 | 1965-12-23 | 0 | 0 | 1964-07-19 | 1965-11-14 | 1965-07-19 | 1966-11-14 | 0 |
| 2018-09-16 | 2 | 1966-02-23 | 0 | 0 | 1965-07-19 | 1966-11-14 | 1966-07-19 | 1967-11-14 | 0 |
| 2018-09-16 | 3 | 1994-10-15 | 0 | 0 | 1993-07-19 | 1994-11-14 | 1994-07-19 | 1995-11-14 | 0 |
| 2018-09-16 | 4 | 1995-10-23 | 0 | 0 | 1994-07-19 | 1995-11-14 | 1995-07-19 | 1996-11-14 | 0 |
| 2018-10-16 | 1 | 1965-12-23 | 0 | 0 | 1964-08-18 | 1965-12-14 | 1965-08-18 | 1966-12-14 | 0 |
| 2018-10-16 | 2 | 1966-02-23 | 0 | 0 | 1965-08-18 | 1966-12-14 | 1966-08-18 | 1967-12-14 | 0 |
| 2018-10-16 | 3 | 1994-10-15 | 0 | 0 | 1993-08-18 | 1994-12-14 | 1994-08-18 | 1995-12-14 | 0 |
| 2018-10-16 | 4 | 1995-10-23 | 0 | 0 | 1994-08-18 | 1995-12-14 | 1995-08-18 | 1996-12-14 | 0 |
| 2018-11-16 | 1 | 1965-12-23 | 0 | 1 | 1964-09-18 | 1965-01-14 | 1965-09-18 | 1966-01-14 | 1 |
| 2018-11-16 | 2 | 1966-02-23 | 0 | 1 | 1965-09-18 | 1966-01-14 | 1966-09-18 | 1967-01-14 | 0 |
| 2018-11-16 | 3 | 1994-10-15 | 0 | 1 | 1993-09-18 | 1994-01-14 | 1994-09-18 | 1995-01-14 | 1 |
| 2018-11-16 | 4 | 1995-10-23 | 0 | 1 | 1994-09-18 | 1995-01-14 | 1995-09-18 | 1996-01-14 | 1 |
| 2018-12-16 | 1 | 1965-12-23 | 0 | 1 | 1964-10-18 | 1965-02-13 | 1965-10-18 | 1966-02-13 | 1 |
| 2018-12-16 | 2 | 1966-02-23 | 0 | 1 | 1965-10-18 | 1966-02-13 | 1966-10-18 | 1967-02-13 | 0 |
| 2018-12-16 | 3 | 1994-10-15 | 0 | 1 | 1993-10-18 | 1994-02-13 | 1994-10-18 | 1995-02-13 | 0 |
| 2018-12-16 | 4 | 1995-10-23 | 0 | 1 | 1994-10-18 | 1995-02-13 | 1995-10-18 | 1996-02-13 | 1 |
+-------------+-----+-------------+-------------+------------+--------------+-------------+--------------+-------------+-------+--+
