Блог

Если у меня есть таблица, которая регистрирует, сколько товаров было продано каждый день, и не заносит в журнал, если было продано 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
  

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

Здесь можно использовать логику, которая утверждает, что все даты присутствуют или нет:

Ниже приведен стандартный 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;
  

Ниже приведен стандартный 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   
  

с выводом

Если есть дни, когда продаж вообще нет или в последний день отчета продажи равны нулю, вы можете использовать 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