SQLize Online / PHPize Online  /  SQLtest Online

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.9) within group(order by pressure ) as perc from weather w group by to_char(wdate, 'YYYY-MM') order by wmonth ) 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') ; select * from tab; select *, rank() over(partition by to_char(wdate, 'YYYY-MM') order by pressure) as t from weather ;