Интересный SQL

[SLIM]

Я уже не малый промежуток своей жизни связан с SQL.
SQL является и подфорумом на данном ресурсе.
Я решил приводить здесь какие-то интересные запросы, с которыми приходилось связываться.
Приводить я буду сначала задачу, а после просмотра рашений других показывать свое решение. Всем будет полезно - полюбому кто-то узнает новое - я ли кто-то другой.
Хотел бы подчеркнуть, что я буду стараться приводить код на T-SQL, но, думаю, большую часть можно будет перевести и на другие синтаксисы и СУБД.
Я, например, часто их перевожу в Access-кий формат.


ЗАДАЧА 1
Начну я с недавно поставленной задачи:
Есть некий идентификатор. Не важно какой, в складской базе это может быть какой-нибудь код позиции. Да, пожалуй возьмем для примеру складскую базу данных.
Есть коды позиций. У каждой позиции есть своя цена. Цена может меняться со временем. Получаем - цена и позиция. Но это безсмысленно иметь, как это обычно бывает, без временных разрезов. Временной разрез здесь - дата установки новой цены.
Итого получаем некую структуру данных, где есть поля КодПозиции, ДатаУстановки, ЗначениеУстановки.

Задача 1: Сделать запрос (соответственно можно из него сделать представление) для вычисления значения цены в заданный момент времени. Т.е. указав дату и код позиции, получить значение цены в этот день. Соответственно, если затребованный день будет позже последней даты установки, то последняя цена и будет являться текущей на это день.

Задача 2: Представим, что цена начинает действовать всегда с понедельника. Даже если цена была установлена в четверг, действовать она станет только на следующей неделе. Нужно сделать запрос (представление), который будет показывать значение цены у позиции по запрошенной неделе.

Доп. данные. Так как скорее всего (но не обязательно) может понадобиться доп. таблица типа календаря, где будут данные, содержащие
Дата, День недели, день месяца, День года
То можно считать что она есть. Хотя ее можно и съэмулировать.

Запрещено создавать временные глобальные и локальные таблицы, соответственно, никаких Delete, Insert, Update и т.д., просто Select и все.
Неделя всегда должна начинается с понедельника.


UPD:
Да, тут неприятность вышла. Данные нужно получить в виде
День, Значение, КодПозиции.

Запрос может возвращать и данные за период. Т.е. несколько дней. А не толко на какой-то опеределнный.

Т.е. можно написать Select * from <ваш запрос> where data between <data1> and <data2>

UPD::
Подчеркну еще раз. Запрос д.б. предствалением (но м.б. и вложенным). Т.е. в запросе не ограничивается дата и код, ограничивается это во внешнем запросе, или в звпросе, юзающего представление

[Хакер]

Что же тут интересного/сложного?

[SLIM]

Да ничего. Не все же гуру SQL. Кому-то интересно будет.

[alibek]

Какая СУБД?
Скажем, в SQL2005 для исторических таблиц, в которых история задается не парой start-end, а только start, удобно использовать CROSS APPLY.

[SLIM]

Удобно, наверное. Не работал на SQL Server 2005, но слышал об APPLY
Интересно, а в представлениях можно юзать TVF? По сути функция должна быт создана заранее.
Я вообще работаю на MS SQL 2000 пока.
Но желательно писать запрос, которы возможно перенести с СУБД на СУБД, я всегда стараюсь такие писать

З.Ы. Вообще в MS SQL много всяких вкусностей

[FireFenix]

Работаем с UNIX-Time

1) SELECT * FROM table WHERE table.date < date ORDER BY table.date DESC
в первом значении будет последняя занесённая дата

2) Тоже самое что и 1), т.к. выбирается последняя дата до текущего момента, и нужно указывать в date понедельник текущего промежутка

[SLIM]

Я же говорю, дата не задается в запросе, запрос возвращает все что есть по дням.
Ограничение же задается при выборе из запроса.

[FireFenix]

Я же говорю, дата не задается в запросе, запрос возвращает все что есть по дням.
Ограничение же задается при выборе из запроса.

и 1 большой запрос выполняется быстрее чем несколько маленьких => опять же лучше изначально задавать всё в 1ом большом и толстом

Интересная была у меня когда-то задачка:
Есть в таблице интервалы чисел

Код: Выделить всё

id
1
2
5
6
9
11

Как найти пропущенные интервалы (3, 4, 7, 8 ) без доп. таблиц и вторичных запросов?

[iGrok]

...т.к. мускуль их не держит =)

Это когда это мускуль "не держал" вложенные запросы?
Отстали от жизни вы, батенька..

[FireFenix]

...т.к. мускуль их не держит =)

Это когда это мускуль "не держал" вложенные запросы?
Отстали от жизни вы, батенька..

Виноват... Он держит, но немного криво... не так как mssql

[SLIM]

Интересная была у меня когда-то задачка:
Есть в таблице интервалы чисел

Код: Выделить всё
id
1
2
5
6
9
11


Как найти пропущенные интервалы (3, 4, 7, 8 ) без доп. таблиц и вторичных запросов?

Вопрос так скажем, примерно с той же области что и мой. Есть несколько идей, как доберусь до нормальной СУБД попробую. Могу сказать, что на мой взгляд это вполне реально. Возможности SQL широки для работы с подобными данными.

и 1 большой запрос выполняется быстрее чем несколько маленьких => опять же лучше изначально задавать всё в 1ом большом и толстом

Это очень удобно.
Вот пример. Для того чтобы взглянуть на продажи магазина, нужно иметь структуру данных типа : ШифрПозиции, Количество, ОборотВРуб, Дата
Вызывает трудность в этом посчитать цену товара. Придется оборот поделить на количество. Но по сути, данные по обороту излишни. Проще хранить данные о цене товара, и только там _где_ нужно, цеплять данные по цене товара и умножать на количество, так узнаем оборот.
Но хранить данные по цене товара в полном объеме типа: День, Цена, ШифрПозиции тоже излишне. Лучше хранить в виде: ШифрПозиции, УстанавливаемаяЦена, ДатаУстановки.

Вот и мы и подобрались к той самой структуре. Основная таблица продаж у нас не перегружена лишней информацией, таблица с ценами не содержит лишней информации также. Остается только этим вопспользоваться.
А теперь представь, тебе нужно каждый день, в разном виде, для разных целей выводить данные по оборотам, например суммируя за неделю, или по нескольким магазинам, или еще чего. И каждый раз, для того чтобы просчитать цену позиции по дням, тебе придется менять твой запрос по датам, и плюс к этому ограничивать по времени и другую таблицу, например таблицу с продажами, и потом все это объединить.

А вот если бы ты имел представление, вьеюшку, которая возвращала бы все данные по ценам, ты бы просто подцепил бы ее например к таблице с продажами, ограничил бы в этом объединении период и не парился бы. Как будто у тебя только таблицу, содержащие все что тебе нужно. И уверяю, на скорость это не отразится. Проверено. Если, правда, только объем будет не слишком большой, и, например, ты затребуешь все данные.

Вот моя позиция, почему так делать хорошо, и так правильнее.
А вот _как_ так сделать я и спрашиваю. Тем мне оно и показалось интересным

[SLIM]

Итак, прошла неделя, ничего интересного не произошло, что грустно.
Но я, как и обещал, предложу свой вариант таки.

Я говорил может понадобиться таблица с датами, полная таблица дат. У меня такая таблица есть, условно назовем ее Calendar.
Пусть она содержит даты с какого-нибудь 1900 года, не важно.

Для того чтобы сформировать такой запрос, нужно получить декартово произведение двух таблиц - первая - это собственно все даты, а вторая - таблица вида "Код позиции", "Дата установки периода действия", "Дата окончания периода действия", "Значение"
Интересно с получением второй таблицы. Здесь удобно применять соотнесенные подзапросы, так, чтобы для каждой позиции выбиралась миимальная дата, где эта дата больше чем дата во внешнем запросе (т.е. не в подзапросе)
Примерно это выглядит так

Код: Выделить всё

select code, data, (select min(f.data) from DataTable f where x.data<f.data and f.code=x.code) enddata , val from DataTable x

Собственно сам соотнесенный подзапрос

Код: Выделить всё

select min(f.data) from DataTable f where x.data<f.data and f.code=x.code

Задача подзапроса - вывести одно значение из таблицы - это раз. Поэтому нужно взять какой-нибудь агрегат, в данном случая я взял min.
Далее, эта минимальная дата должна быть больше чем дата во внешнем запросе, т.е. идти после даты старта, ведь дата старта у нас есть. Поэтому мы соотносим поле data таблицы DataTable (саму к себе) предикатом

Код: Выделить всё

x.data<f.data

, где f - это внутренняя таблица, а x - внешняя. Ну и осталось связать по кодам

Код: Выделить всё

and f.code=x.code

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

Получить декартово произведение не сложно - можно просто через запятую указать две таблицы, можно CROSS JOIN.
Так как в одной таблице все даты, а во второй только даты установки и окончания, то нужно отобрать все дни, и проставить значение на эти дни. Значение на каждый день календаря будет находится между датой установки и датой окончания (ее мы получили). Но мы помним что дата окончания может быть NULL, поэтому если такое произойдет, придется взять текущую. В T-SQL проверка на NULL осуществляется с помощью ISNULL, а получить текущую дату можно с помощью функции GetDate().
Итак, все вместе

Код: Выделить всё

select p.code, val, c.data
from
        (select data  from calendar where data>='20090101' and data<getdate()+1) c,
   (select code, data, (select min(f.data) from DataTable f where x.data<f.data and f.code=x.code) enddata , val
      from DataTable x) p   
where (c.data between p.data and isnull(p.enddata, getdate()))

Вот так вот можно получить на каждый день значение цены.

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

Впрочем, лучше написать чем описать

Код: Выделить всё

select p.code, val, datepart(ww, c.data-1)+1 w, year(c.data) y
from
   (select data  from calendar where data>='20090101' and data<getdate()+1) c,
   (select code, data, (select min(f.data) from DataTable f where x.data<f.data and f.code=x.code) enddata , val
      from DataTable x) p   
where  (c.data between p.data and isnull(p.enddata, getdate()))
   and c.data in(select data from calendar
         where datepart(ww,data-1)=(select datepart(ww,data-1)) and year(data)=(select year(data)) and weekday=7)

Здесь выбирается дата установки - воскресенье.
DatePart(ww,...-1) делается потому что в MS SQL неделя начинается со вторника.
Грубо говоря, запрос с weekday просто отирает нужные даты, т.е. берет например только воскресенья. И уже на воскресенья берет значение цены и берет от воскресенья номер недели.


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

[SLIM]

Решил продолжить.

Итак задача.
Есть набор входных данных.

Код: Выделить всё

Дата, Код, Цена

Получить данные вида

Код: Выделить всё

Дата, Код, ЦенаПредыдущейДаты, ЦенаНаДату, ПредыдущаяДата, Прирост

Ну и как и первый раз - предлагаем свои варианты. Потом выложу свой. А то чувствую я примудрил.

[FireFenix]

MSSQL:

Код: Выделить всё

CREATE TABLE code(
   id int IDENTITY(1,1) NOT NULL,
   code int NOT NULL,
   [date] date NOT NULL,
   price] decimal](10, 2) NOT NULL
) ON PRIMARY


Код: Выделить всё

SELECT * FROM code
--------------------------------------
1   5   2010-01-01   11.10
2   5   2010-01-02   11.20
3   5   2010-01-03   11.30
4   5   2010-01-04   11.50
5   5   2010-01-05   11.60


Фиксированный период:

Код: Выделить всё

SELECT
    T1.[date]
    , T1.code
    , T2.price
    , T1.price
    , T2.[date]
    , (T1.price - T2.price) as 'profit'
FROM
    code as T1
LEFT JOIN
    code as T2
ON
    DAY(T1.[date]) = DAY(T2.[date]) + 1 -- период
AND
    T1.code = T2.code;

Произвольный период:

Код: Выделить всё

SELECT
    T1.[date]
    , T1.code
    , T2.price
    , T1.price
    , T2.[date]
    , (T1.price - T2.price) as 'profit'
FROM
    code as T1
LEFT JOIN
    code as T2
ON
    T2.id = (SELECT TOP(1)
                 T3.id
             FROM
                 code as T3
             WHERE
                 T3.code = T1.code
             AND
                 T1.[date] > T3.[date]
             ORDER BY
                 T3.[date] DESC
             );


Что-то лучше пока в голову не приходит...

Результат:

Код: Выделить всё

2010-01-01   5   NULL   11.10   NULL   NULL
2010-01-02   5   11.10   11.20   2010-01-01   0.10
2010-01-03   5   11.20   11.30   2010-01-02   0.10
2010-01-04   5   11.30   11.50   2010-01-03   0.20
2010-01-05   5   11.50   11.60   2010-01-04   0.10


[SLIM]

Фиксированный период:

Косо если разница будет больше одного дня

Произвольный период:

Этот гуд. Но будет дольше работать чем мой вариант из-за order и left на мой взгляд.
Но вообще да, второй случай - примерно то что я хотел написать.

Есть еще у кого варианты?

[HandKot]

пусть много времени прошло

Код: Выделить всё

declare @calendar table(dt datetime)
Insert Into @calendar(dt) values
('20100101'),
('20100102'),
('20100103'),
('20100104'),
('20100105'),
('20100106'),
('20100107'),
('20100108'),
('20100109'),
('20100110'),
('20100111'),
('20100112'),
('20100113'),
('20100114'),
('20100115')

declare @code table(
   id int IDENTITY(1,1) NOT NULL,
   code int NOT NULL,
   [date] datetime NOT NULL,
   [price] decimal(10, 2) NOT NULL
)

insert into @code(code, [date], price) values
(5, '20100101', 11.10)
,(5, '20100102', 11.20)
,(5, '20100103', 11.30)
,(5, '20100104', 11.50)
,(5, '20100105', 11.60)
,(5, '20100110', 11.70)
,(5, '20100115', 11.80)
,(4, '20100101', 11.10)
,(4, '20100102', 11.20)
,(4, '20100103', 11.30)
,(4, '20100104', 11.50)
,(4, '20100105', 11.60)



Select
   x1.code
   , c.dt
   , x1.price
From (
   Select distinct
      x.code
      , max(x.price) Over (Partition By x.code, x.grp) price
      , min(x.[date]) Over (Partition By x.code, x.grp) b_per
      , max(x.[date]) Over (Partition By x.code, x.grp) e_per
   From (
      Select
         c.code
         , case when n.n = 0 then -1 else c.price end price
         , c.[date]
         , ROW_NUMBER() over (partition by c.code order by c.[date] asc) / 2 grp
      From
         @code c, (values (0), (1)) n(n)) x
   ) x1
join @calendar c on  x1.b_per <= c.dt And c.dt < x1.e_per
order by code, 2