con una tabla table1 como a continuación
+------+-----+------+----------+-----------+ | city | day | hour | car_name | car_count | +------+-----+------+----------+-----------+ | 1 | 12 | 00 | corolla | 8 | | 1 | 12 | 00 | city | 9 | | 1 | 13 | 00 | corolla | 17 | | 1 | 13 | 00 | city | 2 | | 1 | 14 | 00 | corolla | 3 | +------+-----+------+----------+-----------+ para cada city, day , hour debe averiguar la diferencia count_diff en car_count para el corola y la ciudad de car_name , excluyendo aquellos en los que ambos o cualquiera de ellos no está presente. count_diff es esencialmente [ (count of corollas) - (count of city) ]
Rendimiento esperado
+------+-----+------+-----------+ | city | day | hour | count_diff| +------+-----+------+-----------+ | 1 | 12 | 00 | -1 | | 1 | 13 | 00 | 15 | +------+-----+------+-----------+with data as (select city, day, hour , sum(case when car_name = 'corolla' then car_count else 0 end) corolla_count, sum(case when car_name = 'city' then car_count else 0 end) as city_count group by city, day, hour ) select city, day, hour, corolla_count - city_count from data where corrolla_count > 0 and city_count > 0Podrías probar esto.
t=# with a as ( select * , count(1) over a , car_count - lead(car_count) over a count_diff from table1 window a as (partition by city,day,hour) ) select city,day,hour,count_diff from a where count >1 and count_diff is not null; city | day | hour | count_diff ------+-----+--------+------------ 1 | 12 | 00 | -1 1 | 13 | 00 | 15 (2 rows) Time: 0.411 ms