Можно ли получить однозначную сводную стоимость по проекту?

Question

Мы экспортировали нашу платежную историю в bigquery. Я пытаюсь получить общую стоимость проекта, но начинаю думать, что это невозможно сделать, потому что данные включают project.labels это означает, что может быть несколько строк на элемент выставления счета.

Вот запрос, который я только что выполнил:

SELECT   project.labels.key,project.labels.value,service.description,usage_start_time,usage_end_time,project.id,sku.description,cost 
FROM     [our-billing-export] 
WHERE    service.id = "6F81-5844-456A"
  and    usage_start_time = "2018-04-06 19:25:01.510 UTC"
  and    usage_end_time = "2018-04-06 21:25:03.785 UTC"
  and    project.id = "dh-raia"
  and    sku.id = "D973-5D65-BAB2"
order by project.labels.key,project.labels.value,service.id, usage_start_time,usage_end_time,project.id,sku.id,cost

который возвращает это:

Обратите внимание, что у нас есть 3 идентичных стоимости для "Storage PD Capacity", которые, я думаю, в порядке, они, вероятно, представляют 3 разных постоянных диска. Также обратите внимание, что те же 3 стоимости появляются снова, на этот раз для другого project.labels.key,

Моя цель - получить полную стоимость за project.id, Ясно, что я не могу просто выдать

select project.id,sum(cost)
from [our-billing-export]
group by project.id

потому что некоторые расходы будут включены более одного раза (потому что они появляются для нескольких project.labels.keyс).

Я не могу фильтровать по одному project.labels.key потому что мы не гарантируем, что каждый проект имеет одинаковые ярлыки.

Я не могу попытаться устранить дублирование, вызванное такими ярлыками:

SELECT   service.id,sku.id,usage_start_time,usage_end_time, project.id,cost
FROM     [our-billing-export]
GROUP BY  service.id,sku.id ,usage_start_time,usage_end_time,project.id,cost

потому что это исключило бы три действительные позиции, каждая из которых имеет одинаковую стоимость.

Я не могу использовать предложение OVER(), чтобы получить одну метку для каждого клиента, например так:

SELECT   project.labels.key,service.id,usage_start_time,usage_end_time,project.id,sku.id,rownum
FROM     (
         SELECT   project.labels.key,service.id,usage_start_time,usage_end_time,project.id,sku.id,
                  ROW_NUMBER() OVER (PARTITION BY project.id,service.id,usage_start_time,usage_end_time,sku.id,project.labels.key) as rownum
         FROM     [our-billing-export]
         )q
WHERE rownum=1

потому что, когда я это делаю, я получаю сообщение об ошибке Повторное поле 'project.labels.key', поскольку ключ PARTITION BY не допускается.

Итак, насколько я могу судить, нет никакого способа получить окончательный ответ на вопрос "Сколько я потратил на каждый проект?" Я надеюсь, что кто-то может сказать мне, что я неправ, и что есть способ сделать это.

Answer 1

ОК, мне удалось решить эту проблему (с некоторой помощью коллеги)

SELECT service.description
,      sku.description
,      project.name
,      labels
,      cost
FROM (
    SELECT  service.description
    ,       sku.description
    ,       project.name
    ,       group_concat(project.labels.key + ':' + project.labels.value) WITHIN RECORD AS labels
    ,       cost
    FROM [our-billing-export] 
    WHERE usage_start_time = "2018-04-06 19:25:01.510 UTC" 
      AND usage_end_time = "2018-04-06 21:25:03.785 UTC" 
      AND project.id = 'dh-raia' AND cost > 0
      AND sku.id = "D973-5D65-BAB2"
  )

Возвращает правильные расходы

которые затем могут быть агрегированы.

Answer 2

Документация BigQuery здесь и здесь должна быть полезной.

В частности, способ, которым повторяющееся поле приведет к двойному счету, заключается в том, что если вы FLATTEN это поле так, что другие строки дублируются. Со второй ссылки:

"При наличии записи с одним или несколькими значениями для повторяющегося поля FLATTEN создаст несколько записей, по одной для каждого значения в повторяющемся поле. Все остальные поля, выбранные из записи, дублируются в каждой новой выходной записи".

Если вы не сгладите повторяющиеся поля, ваш простой пример запроса (выберите project.id, sum (cost) из группы [our-billing-export] by project.id select) не создаст для вас проблемы с двойным счетом.

Кстати, в качестве альтернативы использованию Legacy SQL и GROUP_CONCAT ... WITHIN RECORD для получения повторяющегося поля, возвращенного в виде объединенной строки, вы также можете использовать оператор TO_JSON_STRING в стандартном SQL. Смотрите пример здесь.

Надеюсь это поможет!