#mysql #sql #google-bigquery
#mysql #sql #google-bigquery
Вопрос:
Если у меня есть таблица, которая регистрирует, сколько товаров было продано каждый день, и не заносит в журнал, если было продано 0 товаров, как мне определить, что у всех моих товаров был день 0 продаж?
Доступный пример
Date Item QuantitySold
Jan 1 coffee 4
Jan 1 tea 1
Jan 2 tea 3
Jan 3 coffee 2
Jan 3 tea 4
Желаемый результат будет примерно таким. Пока я могу перейти к версии, в которой перечислены каждый день и количество проданных товаров, но не могу понять, есть ли способ также «пометить» продукт.
Item TotalSold SoldDaily
coffee 6 FALSE
tea 8 TRUE
Это моя грубая отправная точка — объединение состоит в том, чтобы попытаться принудительно отобразить все даты, но оно по-прежнему пропускает строку для кофе 2 января. И оттуда я не могу понять, как я мог бы потенциально выполнить следующий шаг — я пробовал несколько СЛУЧАЕВ, когда они не сходились.
with dates as (select distinct date from cafetable)
select date,
product,
sum(quantitysold) as quant_sold,
min(quantitysold) as min_sold
from dates c
left join cafetable d on d.date=c.date
group by 1,2
Я не думаю, что я даже хорошо формулирую это. Я работаю с этой таблицей дюжину раз в день, и я чувствую, что этот запрос поджарил мой мозг.
Комментарии:
1. В вашем запросе у вас есть
product
второй столбец, но вCafeTable
, я считаю, что столбец должен бытьitem
вместо этого.
Ответ №1:
Здесь можно использовать логику, которая утверждает, что все даты присутствуют или нет:
Ниже приведен стандартный SQL-запрос BigQuery:
select
c.date,
d.product,
sum(quantitysold) as quant_sold,
min(quantitysold) as min_sold,
case when count(distinct c.date) = (select count(distinct date) from dates)
then TRUE else FALSE end AS SoldDaily
from dates c
left join cafetable d
on d.date = c.date
group by
1, 2;
Комментарии:
1. @attractivetune Если я опубликую там, мне понадобится около 20-30 минут, чтобы провести исследование. Да, Виктор закрывает слишком много вопросов как дубликаты, но не все его дублирующие метки неверны, IMO, только некоторые из них.
Ответ №2:
Ниже приведен стандартный SQL-запрос BigQuery
#standardSQL
select item,
sum(QuantitySold) as TotalSold,
count(*) = date_diff(max(date), min(date), day) 1 as SoldDaily
from `project.dataset.CafeTable`
group by item
Вы можете протестировать, поиграть с приведенными выше примерами данных из вашего вопроса, как в примере ниже
#standardSQL
with `project.dataset.CafeTable` as (
select date '2020-01-01' date, 'coffee' item, 4 QuantitySold union all
select '2020-01-01', 'tea', 1 union all
select '2020-01-02', 'tea', 3 union all
select '2020-01-03', 'coffee', 2 union all
select '2020-01-03', 'tea', 4
)
select item,
sum(QuantitySold) as TotalSold,
count(*) = date_diff(max(date), min(date), day) 1 as SoldDaily
from `project.dataset.CafeTable`
group by item
с выводом
Ответ №3:
Если есть дни, когда продаж вообще нет или в последний день отчета продажи равны нулю, вы можете использовать GENERATE_DATE_ARRAY:
WITH test_table AS (
SELECT DATE '2020-01-01' AS Date, 'coffee' AS Item, 4 AS QuantitySold UNION ALL
SELECT DATE '2020-01-01', 'tea', 1 UNION ALL
SELECT DATE '2020-01-02', 'tea', 3 UNION ALL
SELECT DATE '2020-01-03', 'coffee', 2 UNION ALL
SELECT DATE '2020-01-03', 'tea', 4
)
, all_items_table AS (
SELECT DISTINCT Item
FROM test_table
)
SELECT *
FROM all_items_table
CROSS JOIN UNNEST(GENERATE_DATE_ARRAY('2020-01-01', '2020-01-03')) AS Date
LEFT JOIN test_table USING (Date, Item)
WITH test_table AS (
SELECT DATE '2020-01-01' AS Date, 'coffee' AS Item, 4 AS QuantitySold UNION ALL
SELECT DATE '2020-01-01', 'tea', 1 UNION ALL
SELECT DATE '2020-01-02', 'tea', 3 UNION ALL
SELECT DATE '2020-01-03', 'coffee', 2 UNION ALL
SELECT DATE '2020-01-03', 'tea', 4
)
, all_items_table AS (
SELECT DISTINCT Item
FROM test_table
)
SELECT
Item,
LOGICAL_AND(QuantitySold IS NOT NULL) AS SoldDaily
FROM all_items_table
CROSS JOIN UNNEST(GENERATE_DATE_ARRAY('2020-01-01', '2020-01-03')) AS Date
LEFT JOIN test_table USING (Date, Item)
GROUP BY Item