CREATE TABLE IF NOT EXISTS Failed (fail_date DATE);
CREATE TABLE IF NOT EXISTS Succeeded (success_date DATE);
TRUNCATE TABLE Failed;
INSERT INTO Failed (fail_date) VALUES ('2018-12-28');
INSERT INTO Failed (fail_date) VALUES ('2018-12-29');
INSERT INTO Failed (fail_date) VALUES ('2019-01-04');
INSERT INTO Failed (fail_date) VALUES ('2019-01-05');
TRUNCATE TABLE Succeeded;
INSERT INTO Succeeded (success_date) VALUES ('2018-12-30');
INSERT INTO Succeeded (success_date) VALUES ('2018-12-31');
INSERT INTO Succeeded (success_date) VALUES ('2019-01-01');
INSERT INTO Succeeded (success_date) VALUES ('2019-01-02');
INSERT INTO Succeeded (success_date) VALUES ('2019-01-03');
INSERT INTO Succeeded (success_date) VALUES ('2019-01-06');
select *
from
(
select distinct period_state,
min(date) over(partition by date_sub(date, interval rnk day)) start_date,
max(date) over(partition by date_sub(date, interval rnk day)) end_date
from
(
select success_date date, 'succeeded' period_state,
rank() over(order by success_date) rnk
from Succeeded
where success_date like "2019%"
) t1
union all
select distinct period_state,
min(date) over(partition by date_sub(date, interval rnk day)) start_date,
max(date) over(partition by date_sub(date, interval rnk day)) end_date
from
(
select fail_date date, 'failed' period_state,
rank() over(order by fail_date) rnk
from Failed
where fail_date like "2019%"
) t2
) t
order by start_date
select a.period_state,min(date) start_date,max(date) end_date from
(select
'failed' period_state,
fail_date date,
date_sub(fail_date, interval row_number() over(order by fail_date) day) day_diff
from Failed
union all
select
'succeeded' period_state,
success_date date,
date_sub(success_date, interval row_number() over(order by success_date) day) day_diff
from Succeeded) a
where year(date) = "2019"
group by a.period_state,day_diff
order by start_date asc
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/203731.html
標籤：其他