Заполните пропущенные строки на основе смешанных дат

Я выполняю сопоставление в Informatica IICS и пытаюсь заполнить недостающие строки в своем наборе данных на основе нескольких полей.

Ниже представлена ​​примерная таблица данных. Существует поле ID, поле Week_Start, которое представляет собой начальную дату недели, за которую сообщаются данные, соответствующие поля Week_Number и Year, в которых указываются данные как относящиеся к предыдущему или текущему году. Sales — это количество продаж, совершенных с использованием этого идентификатора, а Sales_Type — категория продаж.

Однако существуют даты, когда конкретный человек не совершал продажи, поэтому строка, соответствующая этим данным, отсутствует. Я хотел бы заполнить эти строки всей связанной информацией, а в поле Sales установить значение 0.

Мои фактические данные имеют 6-недельное окно информации как за предыдущий, так и за текущий год для 7 различных типов продаж. Поэтому я ожидаю, что 6x2x7 = 84 строки на идентификатор. т. е. если у меня есть 100 уникальных идентификаторов, в моей итоговой таблице должно быть 8400 строк.

Таблица с отсутствующими строками:

+----+------------+-------------+---------+-------+------------+
| ID | Week_Start | Week_Number |  Year   | Sales | Sales_Type |
+----+------------+-------------+---------+-------+------------+
|  1 | 01/01/2018 |           1 | Prior   |     1 | A          |
|  1 | 01/08/2018 |           2 | Prior   |     3 | A          |
|  1 | 01/15/2018 |           3 | Prior   |     3 | A          |
|  1 | 01/29/2018 |           5 | Prior   |     4 | A          |
|  1 | 01/01/2019 |           1 | Current |     2 | A          |
|  1 | 01/08/2019 |           2 | Current |     4 | A          |
|  1 | 01/15/2019 |           3 | Current |     1 | A          |
|  1 | 01/22/2019 |           4 | Current |     1 | A          |
|  1 | 01/01/2018 |           1 | Prior   |     1 | B          |
|  1 | 01/08/2018 |           2 | Prior   |     3 | B          |
|  1 | 01/15/2018 |           3 | Prior   |     3 | B          |
|  1 | 01/29/2018 |           5 | Prior   |     4 | B          |
|  1 | 01/01/2019 |           1 | Current |     2 | B          |
|  1 | 01/08/2019 |           2 | Current |     4 | B          |
|  1 | 01/15/2019 |           3 | Current |     1 | B          |
|  1 | 01/22/2019 |           4 | Current |     1 | B          |
+----+------------+-------------+---------+-------+------------+

Ожидаемый результат с заполнением отсутствующих строк:

+----+------------+-------------+---------+-------+------------+
| ID | Week_Start | Week_Number |  Year   | Sales | Sales_Type |
+----+------------+-------------+---------+-------+------------+
|  1 | 01/01/2018 |           1 | Prior   |     1 | A          |
|  1 | 01/08/2018 |           2 | Prior   |     3 | A          |
|  1 | 01/15/2018 |           3 | Prior   |     3 | A          |
|  1 | 01/22/2018 |           4 | Prior   |     0 | A          |
|  1 | 01/29/2018 |           5 | Prior   |     4 | A          |
|  1 | 01/01/2019 |           1 | Current |     2 | A          |
|  1 | 01/08/2019 |           2 | Current |     4 | A          |
|  1 | 01/15/2019 |           3 | Current |     1 | A          |
|  1 | 01/22/2019 |           4 | Current |     1 | A          |
|  1 | 01/29/2019 |           5 | Current |     0 | A          |
|  1 | 01/01/2018 |           1 | Prior   |     1 | B          |
|  1 | 01/08/2018 |           2 | Prior   |     3 | B          |
|  1 | 01/15/2018 |           3 | Prior   |     3 | B          |
|  1 | 01/22/2018 |           4 | Prior   |     0 | B          |
|  1 | 01/29/2018 |           5 | Prior   |     4 | B          |
|  1 | 01/01/2019 |           1 | Current |     2 | B          |
|  1 | 01/08/2019 |           2 | Current |     4 | B          |
|  1 | 01/15/2019 |           3 | Current |     1 | B          |
|  1 | 01/22/2019 |           4 | Current |     1 | B          |
|  1 | 01/29/2019 |           5 | Current |     0 | B          |
+----+------------+-------------+---------+-------+------------+

Я пытался использовать преобразования в ICS, но ни одно из них не достигло того, что я пытаюсь сделать. Мое лучшее предположение о том, как это сделать, заключается в использовании рекурсивного CTE в SQL и использовании сценария SQL для создания этих отсутствующих строк.

Мой вопрос в том, как мне сделать это на нескольких разделах? Меня интересуют не просто пропущенные даты, а пропущенные даты за два года и несколько разных типов продаж. Это еще более усложняется тем фактом, что столбец Week_Start содержит смешанные данные. Мои первые попытки сделать это привели к созданию всех строк между датой в 2018 году и данными в 2019 году.


mysql sql etl informatica informatica-cloud
person user-2147482428    schedule 28.06.2019    source источник
comment
генерация всех строк между датой в 2018 году и данными в 2019 году. Мне кажется, это не так уж плохо. Это как 100 недель * 7 типов = 700 строк. Это разовая задача или нужно генерировать результат на лету?   -  person Paul Spiegel    schedule 28.06.2019
comment
вы должны использовать преобразование выражения, где оно получает один набор значений из генератора последовательности (включая опцию для увеличения на 7 каждый раз, когда генерируется новое значение) и добавляется с 1jan2018 и так далее. Это должно помочь вам достичь еженедельных дат. используйте функцию add-to_date с датой в качестве первого параметра и значением из генератора последовательности в качестве второго значения. ADD_TO_DATE( SHIP_DATE, 'ДД', 7)   -  person yoga    schedule 28.06.2019

Ответы (1)


arrow_upward
1
arrow_downward

Используйте cross join для создания строк и left join для ввода значений:

select w.week_start, w.week_number, ys.year, ys.sales_type,
       coalesce(t.sales, 0) as sales
from (select distinct week_start, week_number from t) w cross join
     (select distinct year, sales_type from t) ys left join
     t
     on t.week_start = w.week_start and
        t.year = ys.year and
        t.sales_type = ys.sales_type;
person Gordon Linoff    schedule 28.06.2019