Блог

Мне нужно было бы решить очень сложную проблему SQL: входные данные выглядят как:

 device_id | install_time
  

И мне нужно было бы получать ежедневные установки и повторные установки (означает, что это устройство уже имело install_time до сегодняшнего дня), как я мог бы быстро сгенерировать этот результат таблицы?

Вот пример результата:

 id1 | 08/24/2020
id1 | 08/23/2020
id1 | 08/22/2020
id2 | 08/22/2020
id3 | 08/23/2020
id3 | 08/22/2020
  

результат должен быть

 date | install | re_install
08/22/2020 | 3 | 0
08/23/2020 | 2 | 2
08/24/2020 | 1 | 1
  

Если я правильно понимаю, вы можете использовать оконные функции и агрегацию:

 select install_time::date,
       count(*) as num_installs,
       count(*) filter (where seqnum > 1) as num_reinstalls
from (select t.*,
             row_number() over (partition by device_id order by install_time) as seqnum
      from t
     ) t
group by install_time::date