Вычисления с группировкой по месяцу и колонке

Question

Дано Table1 Id | Date1 | Date2

Table2 Table1Id | Table2Id

Table3 Id | Name

Надо высчитать количество значений Date2 деленное на количество Date1, сгруппировав по имени в таблице 3 и по месяцу Запрос выполняется для 1 года, так что погрешности не существенны. Date2 и Date1 могут быть Null

СУБД - Postgres

Доработка вопроса по итогам переписки: Структура таблиц:

create table CvInfo(
    OfferAcceptedDate timestamptz,
    OfferSentDate timestamptz,
    id int not null primary key
);
create table CvInfoVacancy(
    CvInfosId int,
    VacanciesId int
);
create table Vacancy(
    id int not null primary key,
    name varchar(100)
);

Таблица CvInfoVacancy связующая, для связи "многие ко многим". Одна из попыток решения (она должна подсказать точнее связи):

with tmp_tab as (
    select
        v."Name" as name
        , date_part('month', cv."OfferAcceptedDate") as MonthAcceptedName
        , date_part('month', cv."OfferSentDate") as MonthSentName
        , 1 as cntAcc
        , 1 as cntSent
        from hr_metrics."CvInfo" as cv
        join hr_metrics."CvInfoVacancy" as civ 
            on civ."CvInfosId" = cv."Id" 
        join hr_metrics."Vacancy" as v 
            on civ."VacanciesId" = v."Id" 
        where cv."OfferSentDate" is not null
        and date_part('year', cv."OfferSentDate") = date_part('year', CURRENT_DATE)
        group by  v."Name" , date_part('month', cv."OfferAcceptedDate"), 
        date_part('month', cv."OfferSentDate")
    )
select  distinct
    tmp_tab."name" as name,
    tmp_tab.MonthSentName as mSent,
    tmp_tab.MonthAcceptedName as mAcc,
    Sum(tmp_tab.cntSent) as sented,
    Sum(tmp_tab.cntacc) as accepted
    from tmp_tab as tmp_tab
group by tmp_tab.name, tmp_tab.MonthSentName, tmp_tab.MonthAcceptedName

Пример данных из первой таблицы:

1 | 01/01/2021 | 31/03/2021
2 | 05/01/2021 | 18/01/2021

то считаем как 1/2 в январе, а в третьем месяце будет +1 (P.S. from @Mike: не понял почему +1 в третьем месяце, там по логике либо -1 либо 0, смотря что на что делить)

Answer 1

select name, month, sum((SRC=1)::int) as AcceptedCount, sum((SRC=2)::int) as SentCount,
       case when sum((SRC=1)::int) = 0 then -1
            else sum((SRC=2)::int)::float / sum((SRC=1)::int) end as Result
  from (
    select v.name, SRC,
           extract('month' from case SRC when 1 then OfferAcceptedDate else OfferSentDate end) as month
      from (select (date_part('year', CURRENT_DATE)::char(4) || '-01-01')::timestamptz as from_date) x
      cross join (select 1 as SRC union all select 2) s
      join CvInfo as cv on (SRC=1 and cv.OfferAcceptedDate >= from_date and cv.OfferAcceptedDate < from_date + interval '1 year')
                        or (SRC=2 and cv.OfferSentDate >= from_date and cv.OfferSentDate < from_date + interval '1 year')
       join CvInfoVacancy as civ on civ.CvInfosId = cv.Id
       join Vacancy as v on civ.VacanciesId = v.Id
      where case SRC when 1 then OfferAcceptedDate else OfferSentDate end is not null
  ) x
  group by name, month

Сначала размножаем записи, что бы было всегда 2 экземпляра. При этом с SRC=1 берем Date1, с SRC=2 Date2. Таким образом даты оказались в одной колонке и по ним можно группировать, не порождая все возможные комбинации. В внешнем запросе разворачиваем их обратно в две колонки. Добавляем деление по вкусу.

Пример на sqlfiddle.com

БЛОГ НА HUSL

Вычисления с группировкой по месяцу и колонке

Ответы (1 шт):