create table weather (
wdate date,
wtemp real,
precip real,
pressure real
);
insert into weather(wdate, wtemp, precip, pressure)
values
('2020-01-01', 4.1, 0.0, 102980.0),
('2020-01-02', 8.6, 1.0, 102160.0),
('2020-01-03', 9.9, 0.6, 101980.0),
('2020-01-04', 5.6, 0.0, 103400.0),
('2020-01-05', 7.8, 0.0, 103380.0),
('2020-01-06', 7.8, 0.8, 102290.0),
('2020-01-07', 8.1, 0.2, 102220.0),
('2020-01-07', 8.1, 0.2, 102220.0),
('2020-01-08', 11.8, 1.0, 102070.0),
('2020-01-09', 11.5, 4.2, 100780.0),
('2020-01-10', 7.1, 0.0, 102100.0),
('2020-01-11', 8.0, 0.6, 102260.0),
('2020-01-12', 10.6, 0.2, 101810.0),
('2020-01-13', 7.5, 5.6, 101060.0),
('2020-01-14', 10.3, 10.0, 99610.0),
('2020-01-15', 9.1, 3.0, 100450.0),
('2020-01-16', 8.9, 4.4, 101520.0),
('2020-01-17', 8.5, 1.0, 101170.0),
('2020-01-18', 4.6, 0.0, 102620.0),
('2020-01-19', 3.7, 0.2, 104280.0),
('2020-01-20', 4.3, 0.0, 104820.0),
('2020-01-21', 3.0, 0.0, 104260.0),
('2020-01-22', 4.3, 0.2, 103810.0),
('2020-01-23', 7.6, 0.0, 103250.0),
('2020-01-24', 6.5, 0.0, 102380.0),
('2020-01-25', 6.3, 0.2, 101930.0),
('2020-01-26', 8.1, 2.4, 101040.0),
('2020-01-27', 8.5, 9.0, 100130.0),
('2020-01-28', 5.4, 0.0, 99490.0),
('2020-01-29', 6.0, 0.0, 100810.0),
('2020-01-30', 10.0, 0.2, 100510.0),
('2020-01-31', 10.7, 1.0, 100640.0),
('2020-02-01', 11.2, 8.0, 100400.0),
('2020-02-02', 9.9, 0.6, 100750.0),
('2020-02-03', 10.3, 0.2, 101200.0),
('2020-02-04', 6.7, 0.0, 102030.0),
('2020-02-05', null, null, 103640.0),
('2020-02-06', 4.8, 0.0, 103250.0),
('2020-02-07', 5.0, 1.2, 102090.0),
('2020-02-08', 9.5, 0.0, 101480.0),
('2020-02-09', 10.4, 13.0, 99660.0),
('2020-02-10', 7.5, 3.0, 99620.0),
('2020-02-11', 6.0, 0.0, 100660.0),
('2020-02-12', 5.6, 10.0, 101440.0),
('2020-02-13', 8.0, 1.0, 99860.0),
('2020-02-13', 8.1, 1.1, 99860.0),
('2020-02-14', 7.2, 3.0, 101600.0),
('2020-02-15', 11.4, 8.0, 100800.0),
('2020-02-16', 12.5, 12.2, 99490.0),
('2020-02-17', 8.4, 0.2, 100830.0),
('2020-02-18', 7.9, 2.4, 101820.0),
('2020-02-19', 5.9, 1.2, 101890.0),
('2020-02-20', 9.5, 5.0, 101100.0),
('2020-02-21', 7.3, 0.0, 102250.0),
('2020-02-22', 11.7, 0.4, 101830.0),
('2020-02-23', 12.0, 2.8, 101660.0),
('2020-02-24', 8.9, 2.0, 101230.0),
('2020-02-25', 7.1, 1.0, 100130.0),
('2020-02-26', 5.1, 4.2, 100280.0),
('2020-02-27', 5.6, 5.0, 100300.0),
('2020-02-28', 6.0, 6.0, 100770.0),
('2020-02-29', 9.4, 7.0, 98710.0),
('2020-03-01', 7.6, 5.0, 98860.0),
('2020-03-02', 7.0, 0.2, 98940.0),
('2020-03-03', 6.0, 0.8, 100130.0),
('2020-03-04', 4.2, 6.0, 100840.0),
('2020-03-05', 5.7, 10.0, 99560.0),
('2020-03-06', 6.6, 0.0, 100350.0),
('2020-03-07', 7.5, 0.4, 101540.0),
('2020-03-08', 11.9, 2.0, 100640.0),
('2020-03-09', 7.8, 7.0, 101120.0),
('2020-03-10', null, null, 100480.0),
('2020-03-11', 11.7, 2.0, 100930.0),
('2020-03-12', 7.7, 1.2, 100930.0),
('2020-03-13', 9.3, 3.0, 101780.0),
('2020-03-14', 10.1, 0.8, 101430.0),
('2020-03-15', 10.9, 2.0, 100850.0),
('2020-03-16', 7.9, 0.0, 101960.0),
('2020-03-17', 9.0, 0.0, 102720.0),
('2020-03-18', 12.5, 0.8, 102480.0),
('2020-03-19', 7.0, 0.8, 102600.0),
('2020-03-20', 8.1, 0.0, 102830.0),
('2020-03-21', 7.4, 0.0, 102690.0),
('2020-03-22', 7.4, 0.0, 102630.0),
('2020-03-23', 8.0, 0.0, 102640.0),
('2020-03-24', 8.0, 0.0, 102570.0),
('2020-03-24', null, 0.0, 102570.0),
('2020-03-24', null, 0.0, 102570.0),
('2020-03-25', 7.3, 0.0, 102460.0),
('2020-03-26', 7.5, 0.0, 102320.0),
('2020-03-27', 7.7, 0.0, 102380.0),
('2020-03-28', 8.2, 0.0, 102800.0),
('2020-03-29', 5.8, 0.0, 103720.0),
('2020-03-30', 6.4, 0.0, 103540.0),
('2020-03-31', 7.0, 0.0, 103160.0),
('2020-04-01', 4.2, 0.0, 102490.0),
('2020-04-02', 9.8, 0.0, 101650.0),
('2020-04-03', 10.5, 0.0, 101730.0),
('2020-04-04', 9.6, 0.0, 101990.0),
('2020-04-05', 13.8, 0.4, 101450.0),
('2020-04-06', 14.5, 0.2, 101560.0),
('2020-04-07', 12.3, 0.0, 102830.0),
('2020-04-08', 15.9, 0.0, 102490.0),
('2020-04-09', 15.9, 0.0, 102450.0),
('2020-04-10', 16.4, 0.0, 102480.0),
('2020-04-11', 17.1, 0.0, 102240.0),
('2020-04-12', 17.2, 0.0, 101490.0),
('2020-04-13', 10.0, 0.0, 102390.0),
('2020-04-14', 8.3, 0.0, 102870.0),
('2020-04-15', 10.8, 0.0, 102310.0),
('2020-04-16', 12.1, 0.0, 101630.0),
('2020-04-17', 11.9, 16.0, 101660.0),
('2020-04-18', 11.3, 1.0, 101750.0),
('2020-04-19', 12.4, 0.0, 102010.0),
('2020-04-20', 12.1, 0.0, 101790.0),
('2020-04-21', 13.5, 0.0, 101640.0),
('2020-04-22', 14.3, 0.0, 101900.0),
('2020-04-23', 15.3, 0.0, 101950.0),
('2020-04-24', 14.9, 0.0, 101610.0),
('2020-04-25', 11.9, 0.0, 101530.0),
('2020-04-26', 12.4, 0.0, 101240.0),
('2020-04-27', 12.8, 3.0, 100780.0),
('2020-04-28', 8.5, 11.0, 100480.0),
('2020-04-29', 10.7, 1.6, 100220.0),
('2020-04-30', 10.2, 4.2, 99510.0),
('2020-05-01', 11.5, 0.2, 99850.0),
('2020-05-02', 11.6, 0.0, 100960.0),
('2020-05-03', 11.9, 0.2, 101550.0),
('2020-05-04', 13.8, 0.0, 101870.0),
('2020-05-05', 11.7, null, 102090.0),
('2020-05-06', null, null, 102510.0),
('2020-05-07', 14.5, 0.0, 102310.0),
('2020-05-08', 17.1, 0.0, 101820.0),
('2020-05-09', 17.8, 0.0, 101300.0),
('2020-05-10', 14.8, 0.0, 101010.0),
('2020-05-11', 8.6, 0.0, 101910.0),
('2020-05-12', 9.3, 0.0, 102130.0),
('2020-05-13', 9.7, 0.0, 101710.0),
('2020-05-14', 8.9, 0.0, 102260.0),
('2020-05-15', 11.3, 0.0, 102380.0),
('2020-05-16', 13.3, 0.0, 102440.0),
('2020-05-17', 14.4, 0.0, 102380.0),
('2020-05-18', 17.1, 0.0, 102360.0),
('2020-05-19', 18.8, 0.0, 102330.0),
('2020-05-20', 20.5, 0.0, 102140.0),
('2020-05-21', 20.3, 0.0, 101920.0),
('2020-05-22', 20.0, 0.0, 101480.0),
('2020-05-23', 15.6, 2.0, 102170.0),
('2020-05-24', 16.5, 0.0, 102970.0),
('2020-05-25', 18.1, 0.0, 103420.0),
('2020-05-26', 19.1, 0.0, 103520.0),
('2020-05-27', 20.0, 0.0, 103510.0),
('2020-05-28', 17.1, 0.0, 103420.0),
('2020-05-29', 17.7, 0.0, 102810.0),
('2020-05-30', 17.8, 0.0, 102330.0),
('2020-05-31', 18.9, 0.0, 102240.0),
('2020-06-01', 19.1, 0.0, 102250.0),
('2020-06-02', 18.9, 0.0, 101950.0),
('2020-06-03', 16.4, 0.4, 100920.0),
('2020-06-04', 13.6, null, 100410.0),
('2020-06-05', null, 1.0, 99710.0),
('2020-06-06', 10.9, 10.0, 99900.0),
('2020-06-07', 13.4, 2.0, 100810.0),
('2020-06-08', 13.5, 0.0, 101820.0),
('2020-06-09', 15.3, 0.0, 102020.0),
('2020-06-10', 14.6, 5.0, 101570.0),
('2020-06-11', 16.2, 0.8, 101170.0),
('2020-06-12', 17.6, 2.0, 100590.0),
('2020-06-13', 18.7, 2.0, 100770.0),
('2020-06-14', 18.0, 0.0, 101360.0),
('2020-06-15', 18.5, 1.0, 101610.0),
('2020-06-16', 19.3, 0.2, 101440.0),
('2020-06-17', 17.7, 11.0, 101210.0),
('2020-06-18', 16.5, 9.8, 101040.0),
('2020-06-19', 17.2, 0.4, 101360.0),
('2020-06-20', 17.5, 0.6, 101930.0),
('2020-06-21', 19.0, 0.6, 101650.0),
('2020-06-22', 17.2, 0.0, 102440.0),
('2020-06-23', 20.2, 0.0, 102370.0),
('2020-06-24', 24.6, 0.0, 102020.0),
('2020-06-25', 25.9, 0.0, 101560.0),
('2020-06-26', 25.3, 3.0, 100920.0),
('2020-06-27', 18.4, 0.8, 100510.0),
('2020-06-28', 17.2, 1.8, 100890.0),
('2020-06-29', 16.9, 0.0, 100980.0),
('2020-06-30', 16.2, 0.6, 100760.0);
with tab as (
select to_char(wdate, 'YYYY-MM') as wmonth,
percentile_cont(0.1) within group(order by pressure ) as perc
from weather w
group by to_char(wdate, 'YYYY-MM')
order by wmonth
)
select *
from tab;
select *
from weather;
select to_char(wdate, 'YYYY-MM') as wmonth,
count(*) filter(where pressure < tab.perc)
from weather w inner join tab on tab.wmonth = to_char(wdate, 'YYYY-MM')
group by to_char(wdate, 'YYYY-MM')
order by to_char(wdate, 'YYYY-MM')
;
Telegram: DB developers channel