Интересный SQL

В этом форуме автор намерен рассказывать о своём нелегком пути становления программистом.

Модератор: SLIM

SLIM
Продвинутый гуру
Продвинутый гуру
Аватара пользователя
 
Сообщения: 1840
Зарегистрирован: 04.04.2008 (Пт) 18:21
Откуда: Краснодар

Интересный SQL

Сообщение SLIM » 08.04.2010 (Чт) 22:34

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


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

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

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

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

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


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

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

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

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

Хакер
Телепат
Телепат
Аватара пользователя
 
Сообщения: 16473
Зарегистрирован: 13.11.2005 (Вс) 2:43
Откуда: Казахстан, Петропавловск

Re: Интересный SQL

Сообщение Хакер » 08.04.2010 (Чт) 22:39

Что же тут интересного/сложного?
—We separate their smiling faces from the rest of their body, Captain.
—That's right! We decapitate them.

SLIM
Продвинутый гуру
Продвинутый гуру
Аватара пользователя
 
Сообщения: 1840
Зарегистрирован: 04.04.2008 (Пт) 18:21
Откуда: Краснодар

Re: Интересный SQL

Сообщение SLIM » 08.04.2010 (Чт) 22:41

Да ничего. Не все же гуру SQL. Кому-то интересно будет.
Пишите жизнь на чистовик.....переписать не удастся.....

alibek
Большой Человек
Большой Человек
 
Сообщения: 14205
Зарегистрирован: 19.04.2002 (Пт) 11:40
Откуда: Russia

Re: Интересный SQL

Сообщение alibek » 09.04.2010 (Пт) 7:59

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

SLIM
Продвинутый гуру
Продвинутый гуру
Аватара пользователя
 
Сообщения: 1840
Зарегистрирован: 04.04.2008 (Пт) 18:21
Откуда: Краснодар

Re: Интересный SQL

Сообщение SLIM » 09.04.2010 (Пт) 18:45

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

З.Ы. Вообще в MS SQL много всяких вкусностей
Пишите жизнь на чистовик.....переписать не удастся.....

FireFenix
Продвинутый гуру
Продвинутый гуру
Аватара пользователя
 
Сообщения: 1640
Зарегистрирован: 25.05.2007 (Пт) 10:24
Откуда: Mugen no Sora

Re: Интересный SQL

Сообщение FireFenix » 10.04.2010 (Сб) 18:31

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

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

2) Тоже самое что и 1), т.к. выбирается последняя дата до текущего момента, и нужно указывать в date понедельник текущего промежутка
Птицей Гермеса меня называют, свои крылья пожирая... сам себя я укрощаю
私はヘルメスの鳥 私は自らの羽根を喰らい 飼い慣らされる

SLIM
Продвинутый гуру
Продвинутый гуру
Аватара пользователя
 
Сообщения: 1840
Зарегистрирован: 04.04.2008 (Пт) 18:21
Откуда: Краснодар

Re: Интересный SQL

Сообщение SLIM » 10.04.2010 (Сб) 19:37

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

FireFenix
Продвинутый гуру
Продвинутый гуру
Аватара пользователя
 
Сообщения: 1640
Зарегистрирован: 25.05.2007 (Пт) 10:24
Откуда: Mugen no Sora

Re: Интересный SQL

Сообщение FireFenix » 10.04.2010 (Сб) 21:22

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

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

Интересная была у меня когда-то задачка:
Есть в таблице интервалы чисел
Код: Выделить всё
id
1
2
5
6
9
11

Как найти пропущенные интервалы (3, 4, 7, 8 ) без доп. таблиц и вторичных запросов?
Последний раз редактировалось FireFenix 10.04.2010 (Сб) 21:40, всего редактировалось 1 раз.
Птицей Гермеса меня называют, свои крылья пожирая... сам себя я укрощаю
私はヘルメスの鳥 私は自らの羽根を喰らい 飼い慣らされる

iGrok
Артефакт VBStreets
Артефакт VBStreets
 
Сообщения: 4272
Зарегистрирован: 10.05.2007 (Чт) 16:11
Откуда: Сетевое сознание

Re: Интересный SQL

Сообщение iGrok » 10.04.2010 (Сб) 21:31

FireFenix писал(а):...т.к. мускуль их не держит =)

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

FireFenix
Продвинутый гуру
Продвинутый гуру
Аватара пользователя
 
Сообщения: 1640
Зарегистрирован: 25.05.2007 (Пт) 10:24
Откуда: Mugen no Sora

Re: Интересный SQL

Сообщение FireFenix » 10.04.2010 (Сб) 21:41

iGrok писал(а):
FireFenix писал(а):...т.к. мускуль их не держит =)

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

Виноват... Он держит, но немного криво... не так как mssql
Птицей Гермеса меня называют, свои крылья пожирая... сам себя я укрощаю
私はヘルメスの鳥 私は自らの羽根を喰らい 飼い慣らされる

SLIM
Продвинутый гуру
Продвинутый гуру
Аватара пользователя
 
Сообщения: 1840
Зарегистрирован: 04.04.2008 (Пт) 18:21
Откуда: Краснодар

Re: Интересный SQL

Сообщение SLIM » 10.04.2010 (Сб) 22:14

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

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


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

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

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


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

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

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

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

SLIM
Продвинутый гуру
Продвинутый гуру
Аватара пользователя
 
Сообщения: 1840
Зарегистрирован: 04.04.2008 (Пт) 18:21
Откуда: Краснодар

Re: Интересный SQL

Сообщение SLIM » 18.04.2010 (Вс) 22:25

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

Я говорил может понадобиться таблица с датами, полная таблица дат. У меня такая таблица есть, условно назовем ее 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
Продвинутый гуру
Продвинутый гуру
Аватара пользователя
 
Сообщения: 1840
Зарегистрирован: 04.04.2008 (Пт) 18:21
Откуда: Краснодар

Re: Интересный SQL

Сообщение SLIM » 07.10.2010 (Чт) 19:28

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

Итак задача.
Есть набор входных данных.
Код: Выделить всё
Дата, Код, Цена


Получить данные вида
Код: Выделить всё
Дата, Код, ЦенаПредыдущейДаты, ЦенаНаДату, ПредыдущаяДата, Прирост


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

FireFenix
Продвинутый гуру
Продвинутый гуру
Аватара пользователя
 
Сообщения: 1640
Зарегистрирован: 25.05.2007 (Пт) 10:24
Откуда: Mugen no Sora

Re: Интересный SQL

Сообщение FireFenix » 07.10.2010 (Чт) 20:34

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
Продвинутый гуру
Продвинутый гуру
Аватара пользователя
 
Сообщения: 1840
Зарегистрирован: 04.04.2008 (Пт) 18:21
Откуда: Краснодар

Re: Интересный SQL

Сообщение SLIM » 07.10.2010 (Чт) 22:17

FireFenix писал(а):Фиксированный период:

Косо если разница будет больше одного дня
FireFenix писал(а):Произвольный период:

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

Есть еще у кого варианты?
Пишите жизнь на чистовик.....переписать не удастся.....

HandKot
Бывалый
Бывалый
Аватара пользователя
 
Сообщения: 283
Зарегистрирован: 28.06.2006 (Ср) 13:34
Откуда: Sergiev Posad

Re: Интересный SQL

Сообщение HandKot » 05.07.2013 (Пт) 14:40

пусть много времени прошло
Код: Выделить всё
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
I Have Nine Lives You Have One Only
THINK!


Вернуться в SLIM

Кто сейчас на конференции

Сейчас этот форум просматривают: нет зарегистрированных пользователей и гости: 1

    TopList