Блог

Главная - Вопросы по программированию - SQL присоединяет таблицу подсчета запасов к таблице даты

SQL присоединяет таблицу подсчета запасов к таблице даты

#mysql #sql #join

#mysql #sql #Присоединиться

Вопрос:

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

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

инвентаризация

 DATE        ID  Qty Count
2014-05-13  123 12  12
2014-05-19  123 -1  11
2014-05-28  123 -1  10
2014-05-29  123 -3  7
2014-05-10  124 5   5
2014-05-15  124 -1  4
2014-05-21  124 -1  3
2014-05-23  124 -3  0

У меня есть таблица, которая включает даты для объединения, но я не уверен, как объединить недостающие даты для нескольких продуктов.

Мне нужен запрос следующим образом. Он должен возвращать подсчеты за выбранный период, но также включать промежуточные даты. 

 DATE        ID  Qty Count
2013-05-01  123 0   0
2013-05-02  123 0   0
2013-05-03  123 0   0
2013-05-04  123 0   0
2013-05-05  123 0   0
2013-05-06  123 0   0
2013-05-07  123 0   0
2013-05-08  123 0   0
2013-05-09  123 0   0
2013-05-10  123 0   0
2013-05-11  123 0   0
2013-05-12  123 0   0
2014-05-13  123 12  12
2013-05-14  123 0   12
2013-05-15  123 0   12
2013-05-16  123 0   12
2013-05-17  123 0   12
2013-05-18  123 0   12
2014-05-19  123 -1  11
2013-05-20  123 0   11
2013-05-21  123 0   11
2013-05-22  123 0   11
2013-05-23  123 0   11
2013-05-24  123 0   11
2013-05-25  123 0   11
2013-05-26  123 0   11
2013-05-27  123 0   11
2014-05-28  123 -1  10
2014-05-29  123 -3  7
2013-05-30  123 0   7
2013-05-31  123 0   7
2013-05-01  124 0   0
2013-05-02  124 0   0
2013-05-03  124 0   0
2013-05-04  124 0   0
2013-05-05  124 0   0
2013-05-06  124 0   0
2013-05-07  124 0   0
2013-05-08  124 0   0
2013-05-09  124 0   0
2014-05-10  124 5   5
2014-05-11  124 0   5
2014-05-12  124 0   5
2014-05-13  124 0   5
2014-05-14  124 0   5
2014-05-15  124 -1  4
2014-05-16  124 0   4
2014-05-17  124 0   4
2014-05-18  124 0   4
2014-05-19  124 0   4
2014-05-20  124 0   4
2014-05-21  124 -1  3
2014-05-22  124 0   3
2014-05-23  124 -3  0
2014-05-24  124 0   0
2014-05-25  124 0   0
2014-05-26  124 0   0
2014-05-27  124 0   0
2014-05-28  124 0   0
2014-05-29  124 0   0
2014-05-30  124 0   0
2014-05-31  124 0   0

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

1. Вы уже пробовали?

Ответ №1:

Используется inv join inv для создания не менее 31 строки и построения таблицы из 31 дня. Затем присоедините идентификаторы и, наконец, исходную таблицу.

 select a.d, a.id, a.qty,
  if(a.id=@lastid, @count:=@count a.qty, @count:=a.count) `count`,
  @lastid:=a.id _lastid
from (
  select a.d, b.id, ifnull(c.qty, 0) qty, ifnull(c.count, 0) `count`
  from (
    select adddate('2014-05-01', @row) d, @row:=@row 1 i
    from inv a
    join inv b
    join (select @row := 0) c
    limit 31) a
  join (
    select distinct id
    from inv) b
  left join inv c on a.d = c.date and b.id = c.id
  order by b.id, a.d) a
join (select @count := 0, @lastid := 0) b;

скрипка

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

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

2. @Каскады, упс. Не понял count , что показывает накопленные значения. Это исправлено.

3. Это ОЧЕНЬ помогло и, по сути, работает. Спасибо. Мои правки заключались в том, что в итоге я использовал существующую таблицу для создания строк дат вместо перекрестного соединения. Перекрестное соединение имело низкую производительность для того, со сколькими продуктами я работал.

Ответ №2:

Вот необходимые шаги:

  1. Получить все даты между двумя заданными датами.
  2. Получите начальный запас по идентификатору. Это: получить первую дату на или после заданной начальной даты для этого идентификатора, прочитать запас этой записи и вычесть ее количество транзакций.
  3. Для каждой даты получаем предыдущий запас. Если есть запись для этой даты, добавьте ее количество транзакций и сравните результат с количеством запасов. Выдает ошибку, если значения не совпадают. (Это потому, что вы храните данные с избытком; количество записи должно равняться количеству предыдущей записи плюс ее собственное количество транзакций. Но данные всегда могут быть противоречивыми, поэтому лучше проверить это.) Показывает новый запас и разницу с предыдущим запасом.

Все это обычно достигается с помощью рекурсивного CTE для дат, производной таблицы для всех начальных запасов в лучшем случае с использованием функции KEEP DENSE_RANK и функции LAG для просмотра предыдущей записи.

  • MySQL не поддерживает рекурсивные CTE — или CTE вообще, если на то пошло. Вы можете эмулировать это с помощью достаточно большой таблицы и переменной.
  • MySQL не поддерживает функцию KEEP DENSE_RANK . Вместо этого вы можете работать с другой производной таблицей, чтобы сначала найти минимальную дату для каждого идентификатора.
  • MySQL не поддерживает функцию ЗАДЕРЖКИ. Вместо этого вы можете работать с переменной в MySQL.

Сказав это, я предлагаю вместо этого использовать язык программирования (Java, C #, PHP, что угодно). Вы просто выбираете необработанные данные с помощью SQL, используете цикл и просто выполняете все обработки для каждой базы записей. Это намного удобнее (и читаемо), чем построение очень сложного запроса, который выполняет все, что необходимо. Вы можете сделать это в SQL, даже в MySQL; я просто не рекомендую это.

Ответ №3:

SQL, который я в конечном итоге использовал для решения этого вопроса, использовал комбинацию ответа @Fabricators (который действительно был правильным ответом) и моих правок.

В итоге я использовал существующую таблицу для создания строк даты вместо перекрестного соединения. Перекрестное соединение имело низкую производительность для того, со сколькими продуктами я работал.

 SELECT
POSTDATE,
IF(@PROD_ID = PRODUCT_ID, @NEW := 0, @NEW := 1) AS New_Product,
(@PROD_ID := PRODUCT_ID) AS PRODUCT_ID,
QUANTITY,
IF(@NEW = 1, @INVENTORY := QUANTITY, @INVENTORY := @INVENTORY QUANTITY) AS 'Count'
FROM (
    (
        SELECT
        POSTDATE,
        PRODUCT_ID,
        QUANTITY
        FROM
        inventory
    )
    UNION ALL
    (
        SELECT
        dateslist_sub.TransDate AS POSTDATE,
        productlist_sub.PRODUCT_ID,
        0 AS QUANTITY,
        FROM
        (
            SELECT
            TransDate
            FROM
            (
                SELECT
                adddate('2013-05-01', @row) AS TransDate,
                @row:=@row 1 i
                FROM
                any_table,
                (SELECT @row := 0) row
            ) datestable
            WHERE
            TransDate <= CURDATE()
        ) dateslist_sub
        cross join (
            SELECT
            PRODUCT_ID
            FROM
            products_table
            ORDER BY
            PRODUCT_ID ASC
        ) productlist_sub
        ORDER BY
        productlist_sub.PRODUCT_ID ASC,
        dateslist_sub.TransDate ASC
    )
    ORDER BY
    PRODUCT_ID ASC,
    POSTDATE ASC
) daily_rows_sub

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

1. Это может работать лучше как ОБЪЕДИНЕНИЕ, ОТЛИЧНОЕ от ОБЪЕДИНЕНИЯ, или ПРЯМОЕ ВНУТРЕННЕЕ СОЕДИНЕНИЕ, чтобы уменьшить количество дубликатов POSTDATE в конечном запросе.