Сравнение двух диапазонов дат в одной таблице

У меня есть таблица с продажами в каждом магазине:

SQL> select * from sales;

        ID ID_STORE DATE       TOTAL
---------- -------- ---------- -------------------------------
         1        1 2010-01-01    500.00
         2        1 2010-01-02    185.00
         3        1 2010-01-03    135.00
         4        1 2009-01-01    165.00
         5        1 2009-01-02    175.00
         6        5 2010-01-01    130.00
         7        5 2010-01-02    135.00
         8        5 2010-01-03    130.00
         9        6 2010-01-01    100.00
         10       6 2010-01-02     12.00
         11       6 2010-01-03     85.00
         12       6 2009-01-01    135.00
         13       6 2009-01-02    400.00
         14       6 2009-01-07     21.00
         15       6 2009-01-08     45.00
         16       8 2009-01-09    123.00
         17       8 2009-01-10    581.00

17 rows selected.

Что мне нужно сделать, так это сравнить два диапазона дат в этой таблице. Допустим, мне нужно знать разницу в продажах с 1 января 2009 г. по 10 января 2009 г. ПРОТИВ 1 января 2010 г. по 10 января 2010 г.

Я хотел бы создать запрос, который возвращает что-то вроде этого:

ID_STORE_A DATE_A     TOTAL_A   ID_STORE_B DATE_B     TOTAL_B
---------- ---------- --------- ---------- ---------- -------------------
         1 2010-01-01    500.00          1 2009-01-01    165.00
         1 2010-01-02    185.00          1 2009-01-02    175.00
         1 2010-01-03    135.00          1 NULL          NULL

         5 2010-01-01    130.00          5 NULL          NULL
         5 2010-01-02    135.00          5 NULL          NULL
         5 2010-01-03    130.00          5 NULL          NULL

         6 2010-01-01    100.00          6 2009-01-01    135.00
         6 2010-01-02     12.00          6 2009-01-02    400.00
         6 2010-01-03     85.00          6 NULL          NULL
         6 NULL          NULL            6 2009-01-07     21.00
         6 NULL          NULL            6 2009-01-08     45.00
         6 NULL          NULL            8 2009-01-09    123.00
         6 NULL          NULL            8 2009-01-10    581.00

Таким образом, даже если в том или ином диапазоне нет продаж, он должен просто заполнить пустое пространство NULL.

Пока что я придумал этот быстрый запрос, но «даты» от продаж к продажам2 иногда различаются в каждой строке:

SELECT sales.*, sales2.*
  FROM sales
  LEFT JOIN sales AS sales2 
    ON (sales.id_store=sales2.id_store)
 WHERE sales.date >= '2010-01-01' 
   AND sales.date <= '2010-01-10' 
   AND sales2.date >= '2009-01-01' 
   AND sales2.date <= '2009-01-10' 
ORDER BY sales.id_store ASC, sales.date ASC, sales2.date ASC

Что мне не хватает?


mysql null date range compare
person Dan H    schedule 05.04.2010    source источник
comment
вам нужно узнать о «GROUP BY» и подзапросах.   -  person lexu    schedule 05.04.2010
comment
С нулевыми значениями с обеих сторон вам, вероятно, понадобится ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ, а не ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ или ПРАВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ.   -  person Jonathan Leffler    schedule 07.04.2010

Ответы (2)


arrow_upward
3
arrow_downward

Используя IBM Informix Dynamic Server 11.50.FC6, я могу использовать эту последовательность SQL, чтобы получить требуемый результат:

Настраивать

CREATE TABLE sales
(
    id       INTEGER NOT NULL,
    id_store INTEGER NOT NULL,
    date     DATE NOT NULL,
    total    DECIMAL(10,2) NOT NULL
);

INSERT INTO sales VALUES( 1, 1, '2010-01-01', 500.00);
INSERT INTO sales VALUES( 2, 1, '2010-01-02', 185.00);
INSERT INTO sales VALUES( 3, 1, '2010-01-03', 135.00);
INSERT INTO sales VALUES( 4, 1, '2009-01-01', 165.00);
INSERT INTO sales VALUES( 5, 1, '2009-01-02', 175.00);
INSERT INTO sales VALUES( 6, 5, '2010-01-01', 130.00);
INSERT INTO sales VALUES( 7, 5, '2010-01-02', 135.00);
INSERT INTO sales VALUES( 8, 5, '2010-01-03', 130.00);
INSERT INTO sales VALUES( 9, 6, '2010-01-01', 100.00);
INSERT INTO sales VALUES(10, 6, '2010-01-02',  12.00);
INSERT INTO sales VALUES(11, 6, '2010-01-03',  85.00);
INSERT INTO sales VALUES(12, 6, '2009-01-01', 135.00);
INSERT INTO sales VALUES(13, 6, '2009-01-02', 400.00);
INSERT INTO sales VALUES(14, 6, '2009-01-07',  21.00);
INSERT INTO sales VALUES(15, 6, '2009-01-08',  45.00);
INSERT INTO sales VALUES(16, 8, '2009-01-09', 123.00);
INSERT INTO sales VALUES(17, 8, '2009-01-10', 581.00);

Запрос

SELECT *
  FROM (SELECT s1.id AS s1id,
               NVL(s1.id_store, s2.id_store) AS s1store,
               NVL(s1.date, MDY(MONTH(s2.date), DAY(s2.date),
                                YEAR(s2.date)+1)) AS s1date,
               s1.total AS s1total,
               s2.id AS s2id,
               NVL(s2.id_store, s1.id_store) AS s2store,
               NVL(s2.date, MDY(MONTH(s1.date), DAY(s1.date),
                                YEAR(s1.date)-1)) AS s2date,
               s2.total AS s2total
          FROM sales AS s1 FULL JOIN sales AS s2
            ON s1.id_store = s2.id_store
           AND s1.date BETWEEN '2010-01-01' AND '2010-01-10'
           AND s2.date BETWEEN '2009-01-01' AND '2009-01-10'
           AND DAY(s1.date)   = DAY(s2.date)
           AND MONTH(s1.date) = MONTH(s2.date)
       ) AS s3
 WHERE s1_date BETWEEN '2010-01-01' AND '2010-01-10'
   AND s2_date BETWEEN '2009-01-01' AND '2009-01-10'
 ORDER BY s1_id_store ASC, s1_date ASC;

Результат

s1id s1store  s1date     s1total  s2id s2store  s2date     s2total
 1       1    2010-01-01  500.00   4       1    2009-01-01  165.00
 2       1    2010-01-02  185.00   5       1    2009-01-02  175.00
 3       1    2010-01-03  135.00           1    2009-01-03             
 6       5    2010-01-01  130.00           5    2009-01-01             
 7       5    2010-01-02  135.00           5    2009-01-02             
 8       5    2010-01-03  130.00           5    2009-01-03             
 9       6    2010-01-01  100.00  12       6    2009-01-01  135.00
10       6    2010-01-02   12.00  13       6    2009-01-02  400.00
11       6    2010-01-03   85.00           6    2009-01-03             
         6    2010-01-07          14       6    2009-01-07   21.00
         6    2010-01-08          15       6    2009-01-08   45.00
         8    2010-01-09          16       8    2009-01-09  123.00
         8    2010-01-10          17       8    2009-01-10  581.00

Объяснение

Чтобы понять это «правильно», потребовалось изрядное количество экспериментов. В Informix есть функция-конструктор DATE MDY (), которая принимает три целочисленных аргумента: месяц, день и год (имя мнемоническое). Он также имеет три функции анализа: ДЕНЬ (), МЕСЯЦ () и ГОД (), которые возвращают день, месяц и год аргумента даты. Внутренний запрос с FULL JOIN дает результаты с нулями как слева, так и справа. Критерий из 5 частей в предложении ON представляется необходимым; в противном случае критерии во внешнем запросе должны быть более сложными и запутанными - если вообще можно заставить его работать. Затем критерии во внешнем отборе гарантируют, что выбраны правильные данные. Одним из преимуществ выражений NVL () во внутреннем запросе является то, что столбцы идентификатора магазина одинаковы и не равны нулю, и ни один столбец даты не является нулевым, поэтому предложение order by может быть проще - для идентификатора магазина и любого столбца даты.

В Informix также можно было бы переработать выражения даты как:

NVL(s1.date, s2.date + 1 UNITS YEAR)
NVL(s2.date, s1.date - 1 UNITS YEAR)

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

В Informix также есть сбой ожидания; вы не можете прибавить или вычесть 1 год к любому 29 февраля или от него - потому что 29 февраля в следующем или предыдущем году нет. Вы должны быть осторожны со своими данными; в противном случае вы можете сравнить данные за 28 февраля 2008 г. с 28 февраля 2009 г. (а также сравнить данные за 28 февраля 2008 г. с 28 февраля 2009 г.). Существует процесс, называемый «двойной бухгалтерией», но это не то, что он подразумевает, и ваши вычисления могут быть запутаны, если «2008-02-29 плюс 1 год» будет 2009-02-28. Informix выдает ошибку; это не намного полезнее. Вы могли бы закодировать хранимую процедуру, вероятно, так, чтобы она возвращала NULL для 29 февраля 2008 г. плюс 1 год, поскольку нет даты, с которой можно было бы сравнивать ее продажи.

Вы должны легко адаптировать арифметику даты к MySQL; остальную часть кода изменять не нужно.

person Jonathan Leffler    schedule 17.04.2010

arrow_upward
1
arrow_downward

Я думаю, проблема в вашем условии соединения. Я не тестировал, но думаю, вы могли бы попробовать что-нибудь вроде

... ON (    sales.id_store = sales2.id_store 
        AND sales.date = ADDDATE(sales2.date, INTERVAL 1 YEAR) 
        ...
       )
person PierrOz    schedule 05.04.2010