У меня есть определение следующей таблицы с демонстрационными данными. В следующей таблице Клиентским продуктом и Датой являются поля ключа
Table One
Customer Product Date SALE
X A 01/01/2010 YES
X A 02/01/2010 YES
X A 03/01/2010 NO
X A 04/01/2010 NO
X A 05/01/2010 YES
X A 06/01/2010 NO
X A 07/01/2010 NO
X A 08/01/2010 NO
X A 09/01/2010 YES
X A 10/01/2010 YES
X A 11/01/2010 NO
X A 12/01/2010 YES
В приведенной выше таблице я должен найти N или> N последовательные записи, где не было никакой продажи, Сумма продажи была 'НЕТ', Например, если бы N равняется 2, набор результатов возвратил бы следующее
Customer Product Date SALE
X A 03/01/2010 NO
X A 04/01/2010 NO
X A 06/01/2010 NO
X A 07/01/2010 NO
X A 08/01/2010 NO
Может кто-то помогать мне с SQL-запросом получить желаемые результаты. Я использую SQL Server 2005. Я начал играть использование ROW_NUMBER () И пункты РАЗДЕЛА, но никакая удача. Спасибо за любую справку
Вам нужно сопоставить свою таблицу с самим собой, как если бы там 2 стола. Итак, вы используете два псевдонима, o1 и o2, для ссылки на вашу таблицу:
SELECT DISTINCT o1.customer, o1.product, o1.datum, o1.sale
FROM one o1, one o2
WHERE (o1.datum = o2.datum-1 OR o1.datum = o2.datum +1)
AND o1.sale = 'NO'
AND o2.sale = 'NO';
customer | product | datum | sale
----------+---------+------------+------
X | A | 2010-01-03 | NO
X | A | 2010-01-04 | NO
X | A | 2010-01-06 | NO
X | A | 2010-01-07 | NO
X | A | 2010-01-08 | NO
Обратите внимание, что я выполнил запрос в базе данных postgresql - возможно, синтаксис отличается на ms-sql-server, возможно, на псевдониме 'FROM one AS o1' возможно, а может быть, вы не можете складывать / вычитать таким образом.
Другой подход, вдохновленный последней строкой жевала.
Получить - для данной даты первая дата с YES позже, а последняя дата с YES раньше. Они образуют границу, в которую должны вписаться наши даты.
SELECT (o1.datum),
MAX (o3.datum) - MIN (o2.datum) AS diff
FROM one o1, one o2, one o3
WHERE o1.sale = 'NO'
AND o3.datum <
(SELECT MIN (datum)
FROM one
WHERE datum >= o1.datum
AND SALE = 'YES')
AND o2.datum >
(SELECT MAX (datum)
FROM one
WHERE datum <= o1.datum
AND SALE = 'YES')
GROUP BY o1.datum
HAVING MAX (o3.datum) - MIN (o2.datum) >= 2
ORDER BY o1.datum;
Может быть, здесь нужна какая-то оптимизация, потому что первая таблица участвует в запросе 5 раз. :)
Хорошо, нам нужен переменный ответ. Мы ищем дату, где у нас есть N следующих дат, все с полем продажи NO.
SELECT d1.datum
FROM one d1, one d2, i
WHERE d1.sale = 'NO' AND d2.sale = 'NO'
AND d1.datum = (d2.datum - i)
AND i > 0 AND i < 4
GROUP BY d1.datum
HAVING COUNT (*) = 3;
Это даст нам дату, которую мы используем для подзапроса.
Примечания:
Я использовал datum вместо date, потому что date - зарезервированное ключевое слово в postgresql.
В Oracle вы можете использовать фиктивную виртуальную таблицу, которая содержит все, что вы просите, например 'SELCT foo FROM dual WHERE foo in (1, 2, 3);' что даст вам 1, 2, 3, если я правильно помню. В зависимости от поставщика могут быть другие уловки для получения последовательности от 1 до N. Я создал таблицу i со столбцом i и заполнил ее значениями от 1 до 100, и я ожидаю, что N не превысит 100; Начиная с нескольких версий, postgresql содержит функцию generate_series (from, to), которая также решит проблему и может иметь сходство с решениями для вашей конкретной базы данных. Но таблица i должна работать независимо от поставщика.
если N == 17, вам нужно изменить 3 места с 3 на 17.
Окончательный запрос будет:
SELECT o4.*
FROM one o3, one o4
WHERE o3.datum = (
SELECT d1.datum
FROM one d1, one d2, i
WHERE d1.sale = 'NO' AND d2.sale = 'NO'
AND d1.datum = (d2.datum - i)
AND i > 0 AND i <= 3
GROUP BY d1.datum
HAVING COUNT (*) = 3)
AND o4.datum <= o3.datum + 3
AND o4.datum >= o3.datum;
customer | product | datum | sale
----------+---------+------------+------
X | A | 2010-02-06 | NO
X | A | 2010-02-07 | NO
X | A | 2010-02-08 | NO
X | A | 2010-02-09 | NO
Спасибо всем за размещение своего решения. Думал, тоже со всеми поделюсь своим решением. Как к сведению, я получил это решение от другого участника форума SQL Server Central. Я определенно не возьму на себя ответственность за это решение.
DECLARE @CNT INT
SELECT @CNT = 3
SELECT * FROM
(
SELECT
[Customer], [Product], [Date], [Sale], groupID,
COUNT(*) OVER (PARTITION BY [Customer], [Product], [Sale], groupID) AS groupCnt
FROM
(
SELECT
[Customer], [Product], [Date], [Sale],
ROW_NUMBER() OVER (PARTITION BY [Customer], [Product] ORDER BY [Date])
- ROW_NUMBER() OVER (PARTITION BY [Customer], [Product], [Sale] ORDER BY [Date]) AS groupID
FROM
[TableSales]
) T1
) T2
WHERE
T2.[Sale] = 'NO' AND T2.[groupCnt] >= @CNT