Блог

Главная - Вопросы по программированию - Создание ежедневной используемой таблицы с нулями при NULL

Создание ежедневной используемой таблицы с нулями при NULL

#sql #create-table #days

#sql #создать таблицу #дни

Вопрос:

Привет, сообщество стеков,

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

Мои мысли о том, что при использовании даты из таблицы date, что мое ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ с ISNULL в поле приведет к 0, но нет.

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

 SELECT  
DD.DATE,
DE.PRODUCT_CODE,
--OOC = OUT OF CONTEXT, EITHER ISN'T CHARGEABLE OR ISN'T CURRENTLY ACTIVE
ISNULL(SUM(LIDV.QTY - LIDV.QTYSUB),0),
OD.LOCATION,
OD.SOURCE
FROM Dim_Date AS DD
LEFT OUTER JOIN ORDERv_DatesDays AS OD ON DD.DATE BETWEEN OD.SHIP_DATE AND OD.adjRETURN_DATE
LEFT OUTER JOIN FACT_Orders_LIDs AS LIDV ON LIDV.SORDERID_DAX = OD.SORDERID_DAX
LEFT OUTER JOIN DIM_ECODES AS DE ON DE.PRODUCT_CODE = LIDV.eCODE
WHERE 
--DD.DATE = '3/1/2017' AND
DD.DATE BETWEEN '1/1/2017' AND EOMONTH( DATEADD( MONTH , -1, CURRENT_TIMESTAMP ) ) AND 
DE.PRODUCT_CODE = '07316-' AND 
YEAR(DD.DATE) = 2017
GROUP BY 
DD.DATE,
DE.PRODUCT_CODE,
OD.LOCATION,
OD.SOURCE
ORDER BY
DD.DATE

недостающие даты в красном квадрате

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

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

Комментарии:

1. Кстати, я рекомендую вам использовать даты в формате ‘2000-12-31’ в ваших жестко закодированных строковых константах

2. Почему запрос на скриншоте и запрос в вопросе отличаются?

3. Это не так, это то же самое, только без раздела «создать таблицу» и окончательного предложения order by.

4. И предложение where отличается.. Итак, тогда возникает вопрос: «к какому из этих разных запросов относится скриншот resultsgrid?»

Ответ №1:

Это ГДЕ предикат убивает ваше левое соединение:

 DE.PRODUCT_CODE = '07316-' AND

Если product_code 07316 не был «предоставлен взаймы» (или что-то еще) в период с 24 февраля по 6 апреля, тогда все эти строки выглядели бы так:

 DATE        PRODUCT_CODE  INUSE LOCATION
2017-02-25  NULL          NULL  NULL
2017-02-26  NULL          NULL  NULL
2017-02-27  NULL          NULL  NULL
2017-02-28  NULL          NULL  NULL
...
2017-04-05  NULL          NULL  NULL

Но это значение NULL in product_code означает, что когда в предложении where задается вопрос «равно ли значение NULL 07316- ?», ответ является ложным, поэтому строка исчезает из результирующего набора

Рассмотрим

 LEFT OUTER JOIN DIM_ECODES AS DE 
ON 
  DE.PRODUCT_CODE = LIDV.eCODE AND 
  DE.PRODUCT_CODE = '07316-'

Возможно, вы также захотите внести некоторые изменения в блок ВЫБОРА:

 '07316-' as PRODUCT_CODE,
COALESCE(INUSE,0) AS INUSE

Для вас может иметь больше смысла написать это так:

 FROM 
  Dim_Date AS DD
  LEFT OUTER JOIN 
  (
    SELECT 
      OD.SHIP_DATE, 
      OD.adjRETURN_DATE,
      LIDV.QTY,
      LIDV.QTYSUB,
      OD.LOCATION,
      OD.SOURCE
    FROM
      ORDERv_DatesDays AS OD 
      INNER JOIN FACT_Orders_LIDs AS LIDV ON LIDV.SORDERID_DAX = OD.SORDERID_DAX
      INNER JOIN DIM_ECODES AS DE ON DE.PRODUCT_CODE = LIDV.eCODE
    WHERE
      DE.PRODUCT_CODE = '07316-'
  ) x
  ON DD.DATE BETWEEN x.SHIP_DATE AND x.adjRETURN_DATE

WHERE

Это «список дат слева» и «любые соответствующие данные, которые уже объединены и где справа»

Следует также отметить, что если вы делаете это для нескольких кодов продуктов, чтобы предотвратить только одну строку даты, если оба продукта 07316 и 07317 используются 28 февраля, вам необходимо:

 FROM 
  (
    SELECT DISTINCT DD.DATE, DE.PRODUCT_CODE 
    FROM Dim_Date AS DD CROSS JOIN DIM_ECODES DE
    WHERE ..date range clause..
  )

Это берет ваш список дат и пересекает его с вашим списком кодов продуктов, поэтому вы можете быть уверены, что есть хотя бы эти две строки:

 2017-02-28 07316-
2017-02-28 07317-

Затем, когда вы слева соединяете продукты по дате и коду продукта, данные обеих этих строк сохраняются после левого соединения и становятся связанными с nulls:

 2017-02-28 07316- NULL NULL 
2017-02-28 07317- NULL NULL

Без этого ПЕРЕСЕЧЕНИЯ у вас будет только одна строка (null в коде продукта)

Комментарии:

1. Я специально использую этот код продукта (из более чем 300 тыс. кодов), поэтому у меня есть небольшой набор образцов, пока я работаю над своим кодом, вся моя таблица состоит из нескольких сотен миллионов строк. Результаты одинаковы, независимо от того, фильтрую ли я по этому коду в предложении WHERE или нет.

2. При условии, что эти строки действительно находятся в таблице dates, тогда эти строки где -то представлены, но у них нет кода продукта 07316, поэтому вы не можете найти их таким образом. Если вы запустите запрос без наличия кода продукта и получите свой several hundred million rows * your number of date rows (т. Е. миллиарды строк), Вы все равно найдете их только один раз в результирующем наборе, если ни один продукт не был «в использовании» в эти даты

3. Ваш метод «я хочу, чтобы в 2017 году были все 365 дат, плюс только те дни, когда продукты совпадают» является правильным ( dates LEFT JOIN products WHERE date in 2017 ), но упоминание в предложении WHERE любой таблицы, которая находится справа от «левого соединения», преобразует соединение во внутреннее (эффективно), потому что все нулиЛЕВЫЕ вводные элементы удаляются с помощью WHERE . Любое из ваших других объединений может завершиться неудачно, и даты будут сохранены.. если только они не будут отфильтрованы впоследствии

4. Да, недостающие даты указаны в таблице дат, но, как вы правильно предположили, у меня нет используемых данных для кода продукта для некоторых дат, поэтому я пытаюсь представить 0 для тех неиспользуемых дат (если это имеет смысл). Комментарий в несколько сотен миллионов строк — это таблица со всеми кодами наших продуктов, в которой отсутствуют только даты, подобные тем, что на скриншоте. После этого не было бы миллиардов. Не знал, что в предложении left join и RIGHT table where, СПАСИБО!

5. Добавлено еще одно замечание о том, делаете ли вы это для нескольких продуктов; это может быть важно для вас, чтобы рассмотреть