SQLize Online / PHPize Online  /  SQLtest Online

--drop table AAA; --drop table BBB; create table AAA as select * from ( values (1,'qqww') , (2,'ttwe') , (3,'yaya') ) as t(id, str); select * from AAA; create table BBB as select * from ( values (3, '2024-01-22'::timestamp ) , (8, '2024-02-05'::timestamp ) , (2, '2024-11-11'::timestamp ) ) as t( id, dt ); select * from BBB; create table CCC as select * from BBB union all select id, now() as dt from BBB ; select max(rn), id from ( select row_number() over(partition by id) rn, id, dt from CCC group by id, dt ) t group by id ; select id, dt from ( select c1.dt, c1.id, c2.id as ttid from CCC c1 left join CCC c2 on c1.id = c2.id and c1.dt>c2.dt ) t where ttid is not null ; select * from ccc; select id, dt, lag(dt, id) over(order by dt) prev from CCC; select id, dt from ( select max(dt) over(partition by id ) maxdt, id, dt from CCC )t where dt = maxdt ; with t as ( select row_number() over(partition by id order by dt) rn, id, dt from CCC group by id, dt ) select rn, id, dt from ( select max(rn) over(partition by id) mrn, rn, id, dt from t )t2 where mrn = rn ; --over(partition by id, dt) with t as ( select row_number() over(partition by id order by dt) rn, id, dt from CCC group by id, dt ) select * from t ; --t1.max(rn), t1id, (select distinct t.dt from t where id = t.id ) --from t t1 join t t2 om t1.id = t2.id --group by id select * from CCC; explain select id from BBB where not exists(select id from AAA where BBB.id = AAA.id); explain select id from BBB where id not in (select id from AAA where BBB.id = AAA.id); explain select BBB.id from BBB join AAA on BBB.id!= AAA.id; explain select BBB.id from BBB left join AAA on BBB.id= AAA.id; explain select * from ( select AAA.id as a1, BBB.id as b1 from BBB left join AAA on AAA.id=BBB.id ) t where a1 is not null; /** tests ~~~ - написать анти-джойн - получить план запроса / обсудить - убрать дубли - функции в PG SQL - что такое курсор # OLAP - использование индексов - способы оптимизации # SAS (для L3) , bash - роабота с БД (path-throught vs proc sql + lib ) - логи SAS - как найти наиболее свежий ф-л # webapp / python (AF) / - общая архитектура (что и для чего) **/