Дублируются значения при запросе с generate_series и left join?

Question

[Koteezy]

Всем привет, не могу понять почему в запросе количество предметов считается неправильно, подскажите, в чем может быть косяк?

select 
  dates.d as day,
  count(v.*) as views_count,
  count(i.*) as items_count
from (
    select d from generate_series('2018-12-01'::date, '2018-12-30', '1 day' ) as d
) as dates
left join views as v on v.created_at::date = dates.d
left join items as i on i.created_at::date = dates.d
group by day order by day desc;

Посмотреть в работе можно на dbfiddle

Comments

[Сергей Горностаев]

Какое отношение тег MySQL имеет к вопросу?

[Koteezy]

Сергей Горностаев, Ну почти тот же sql, решил просто чуть больше людей охватить

[Сергей Горностаев]

Koteezy, поправил ваш вопрос, а то придёт модератор и удалит его за нарушение пунктов 3.1 и 3.2 правил.

[Koteezy]

Сергей Горностаев, Я понял, спасибо

[SharuPoNemnogu]

count distinct

[Koteezy]

SharuPoNemnogu, Не катит, в реальной базе нет уникальных айдишников для просмотров :/

Answer 1

[Koteezy]

На Stackoverflow дали ответ, и он исправил ситуацию.

select 
    dates.d as day,
    coalesce(v.count, 0) as views_count,
    coalesce(i.count, 0) as items_count
from generate_series('2018-12-26'::date, '2018-12-30', '1 day' ) as dates(d)
left join (
    select created_at::date as day, count(*)
    from views 
    group by day
    ) as v on v.day = dates.d
left join (
    select created_at::date as day, count(*)
    from items 
    group by day
    ) as i on i.day = dates.d
order by day desc

Answer 2

[Melkij]

Запрос в вопросе делает cross join views на items и разумеется данные в результате будут совершенно верные, но бессмысленные.

Не думаю, что перебирать потенциально большие views и items полностью не прокидывая заведомо известные условия поиска по датам - хорошая мысль даже для аналитического запроса.

select d as day,
(select count(*) from views where created_at >= d and created_at < (d + interval '1 day')) as views_count,
(select count(*) from items where created_at >= d and created_at < (d + interval '1 day')) as items_count
from generate_series('2018-12-26'::date, '2018-12-30', '1 day' ) as dates(d)
order by day desc

Либо похожим образом

select d as day, views_count, items_count
from generate_series('2018-12-26'::date, '2018-12-30', '1 day' ) as dates(d)
left join lateral (select count(*) as views_count from views where created_at >= d and created_at < (d + interval '1 day'))  as v on true
left join lateral (select count(*) as views_count from items where created_at >= d and created_at < (d + interval '1 day'))  as i on true
order by day desc

И посмотреть, что по этому поводу думает планировщик