Наиболее эффективный способ разделения строк по имени и последующего переноса в один столбец для каждого имени

Question

Я использую стандартный SQL в Google Bigquery. Итак, у меня есть некоторые данные о показателях в этом формате:

Date        | metric_name  | metric_level
01/02/2019  | metric_one   | 1
02/03/2019  | metric_one   | 2
14/02/2019  | metric_two   | 6
17/02/2019  | metric_two   | 4
01/03/2019  | metric_three | 2
10/03/2019  | metric_three | 7

Я хочу получить его в этом формате, история дат возвращается на один год, а затем для каждой даты заполняется каждая метрика. Если у метрики нет данных на определенную дату, тогда используется самая последняя точка данных:

Date        | metric_one   | metric_two   | metric_three
..........
01/02/2019  | 1            | null         | null
02/02/2019  | 1            | null         | null
03/02/2019  | 1            | null         | null
...........
...........
13/02/2019  | 1            | null         | null
14/02/2019  | 1            | 6            | null
15/02/2019  | 1            | 6            | null
...........
...........
09/03/2019  | 2            | 4            | 2
10/03/2019  | 2            | 4            | 7
11/03/2019  | 2            | 4            | 7
...........

и так далее.

Мне удалось написать некоторый код, который делает это, но я хочу знать, есть ли более эффективный способ сделать это. На самом деле существует более трех метрик, поэтому, если я смогу сделать его более эффективным, то это сэкономит много ресурсов в долгосрочной перспективе.

Это мой код

    WITH date_arr AS(

        SELECT 
        date

        FROM UNNEST(
            GENERATE_DATE_ARRAY(
                DATE_SUB(CURRENT_DATE(),INTERVAL 365 DAY), 
                CURRENT_DATE(), 
                INTERVAL 1 day
            )
        ) AS date

    ),

    metric_one_raw AS (

        SELECT 
        date,
        metric_level

        FROM database
        WHERE metric_name = 'metric_one'

    ),

    metric_one_gapless AS (

        SELECT
        d.date AS date,
        IFNULL(metric_level, LAST_VALUE(metric_level IGNORE NULLS) OVER(window_latest)) AS metric_one

        FROM date_arr d
        LEFT JOIN metric_one_raw i
        ON d.date = i.date
        WINDOW window_latest AS (ORDER BY d.date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

    ),

    metric_two_raw AS (

        SELECT 
        date,
        metric_level

        FROM database
        WHERE metric_name = 'metric_two'

    ),

    metric_two_gapless AS (

        SELECT
        d.date AS date,
        IFNULL(metric_level, LAST_VALUE(metric_level IGNORE NULLS) OVER(window_latest)) AS metric_two

        FROM date_arr d
        LEFT JOIN metric_two_raw i
        ON d.date = i.date
        WINDOW window_latest AS (ORDER BY d.date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

    ),

    metric_three_raw AS (

        SELECT 
        date,
        metric_level

        FROM database
        WHERE metric_name = 'metric_three'

    ),

    metric_three_gapless AS (

        SELECT
        d.date AS date,
        IFNULL(metric_level, LAST_VALUE(metric_level IGNORE NULLS) OVER(window_latest)) AS metric_three

        FROM date_arr d
        LEFT JOIN metric_three_raw i
        ON d.date = i.date
        WINDOW window_latest AS (ORDER BY d.date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

    )

    SELECT
    *
    FROM metric_one_gapless
    LEFT JOIN metric_two_gapless USING(date)
    LEFT JOIN metric_three_gapless USING(date)

Надеюсь, что это имеет смысл. Заранее спасибо!

Answer 1

Вы можете сделать следующее:

• Генерация даты
• Использовать cross join чтобы получить все строки
• Использовать left join внести ваши данные
• использование last_value() заполнить NULL ценности.

В другой базе данных я бы предпочел lag(ignore nulls), но BigQuery не поддерживает это.

Так:

select d, m.metric,
       coalesce(mm.metric_level,
                last_value(mm.metric_level ignore nulls) over (partition by m.metric order by d)
               ) as metric_level
from (select distinct metric from metrics) m cross join
     unnest(gnerate_date_array(date_sub(current_date(), interval 1 year), interval 1 day) d left join
     metrics mm
     on mm.metric = m.metric and mm.date = d;

Answer 2

После некоторых исследований я придумал что-то, потому что вы используете левое соединение, и будет более одного или даже переменного количества левых соединений, а также вы не можете использовать declareв веб-интерфейсе BigQuery вам, вероятно, лучше использовать функцию API Rest BigQuery, вы можете найти здесь зависимости, вы можете использовать кодирование C#, GO, JAVA, NODE.JS, PHP, PYTHON или RUBY, это позволит вам назначить в переменной количество метрик, поэтому я рекомендую сначала сделать выбор, чтобы узнать, сколько метрик есть, а затем вы можете сохранить их в переменную, а затем выполнить цикл для выполнения левых соединений, которые вы хотите.

Я надеюсь, что эта информация поможет вам, и я здесь, если вам нужна дополнительная информация.