Блог

Обычно я создаю веб-инструменты с помощью базы данных MySQL, и мои навыки SQL не настолько продвинуты. Я был брошен на создание отчета, и это над моей головой.

У меня есть таблица, в которую каждые 4 часа задание вставляет количество продаж на продукт за предыдущие 4 часа. Один столбец этой таблицы становится датой, а другой столбец становится двузначным числом, представляющим час (начиная с полуночи, 4-часовые интервалы будут: 00,04,08,12,16,20).

Таблица выглядит следующим образом:

 Item Color  Size    Hour    Date           Sales
ABC Yellow  S       00      2011-09-10      432
DEF Red     M       00      2011-09-10      1324
GHI Blue    L       00      2011-09-10      567
JKL Tan     XL      00      2011-09-10      967
MNO Green   S       00      2011-09-10      457
ABC Yellow  L       04      2011-09-10      852
  

Мне нужно создать отчет, в котором кто-то может перечислить все продажи за период загрузки (скажем, они выбирают загрузку ’08’ для всех продаж между 4: 00 и 7: 59 утра. Эта часть проста, но мне также нужно добавить % разницы между предыдущими 4, 8 и 12-часовыми периодами.

Например (и только при просмотре продукта ‘ABC’ сверху): на странице отчета кто-то запрашивает отчет 2011-09-10 для отчета Midnight (’04’), который будет отчетом, загруженным в 4: 00 утра для продаж между 12: 00 и 03:59 утра:

 **8:00pm - 11:59am Report**
Item  Color   Size  Date        Sales   4_hour_diff  8_hour_diff 12_hour_diff
ABC   Yellow  S     2011-09-10  852     1.9723       ---         ---
  

(разница в 4 часа заключается в том, что продажи были почти на 200% выше, чем за предыдущий 4-часовой период — с «00» или с 08:00 вечера до 11:59 утра ранее утром.

Я знаю, что могу использовать логику на стороне сервера приложений (в данном случае ColdFusion), чтобы обрабатывать столбец часов, идущий ниже 00 (изменение даты на -1). Я думаю, что мне здесь нужен подзапрос для каждого поля «diff», где каждый подзапрос находил бы продажи за предыдущий период времени и делил их на продажи основного выбора.

Я понимаю концепцию вложенного запроса, но мне никогда не приходилось его писать (если это то, что я должен пытаться сделать здесь). Кроме того, у меня никогда раньше не было MS Sequel. Заранее спасибо за любую помощь или советы — высоко ценится!

Попробуйте этот скрипт:

 use tempdb
GO

declare @date0 date, @date1 date, @date2 date, @date3 date
declare @hour0 int, @hour1 int, @hour2 int, @hour3 int

set @date0='20110910'
set @hour0=4


create table #tblSales(Item varchar(5), Color varchar(20),  Size varchar(2), [Hour] tinyint,  [Date] date, Sales int)

insert into #tblSales (Item, Color, Size, [Hour],  [Date], Sales) values ('ABC', 'Yellow',  'S',0, '20110910', 432)
insert into #tblSales (Item, Color, Size, [Hour],  [Date], Sales) values ('DEF', 'Red',     'M',0, '20110910',1324)
insert into #tblSales (Item, Color, Size, [Hour],  [Date], Sales) values ('GHI', 'Blue',    'L',0,  '20110910',567)
insert into #tblSales (Item, Color, Size, [Hour],  [Date], Sales) values ('JKL', 'Tan',     'XL',0, '20110910',967)
insert into #tblSales (Item, Color, Size, [Hour],  [Date], Sales) values ('MNO', 'Green',   'S',0,  '20110910',457)
insert into #tblSales (Item, Color, Size, [Hour],  [Date], Sales) values ('ABC', 'Yellow',  'L',4,  '20110910',852)
insert into #tblSales (Item, Color, Size, [Hour],  [Date], Sales) values ('ABC', 'Yellow',  'L',8,  '20110909',1852)

--this part can be done in WHILE
set @hour1=@hour0-4
set @date1=@date0
if @hour1<0
    begin
        set @hour1=12
        set @date1=DATEADD(dd,-1,@date0)
    end

set @hour2=@hour1-4
set @date2=@date1
if @hour2<0
    begin
        set @hour2=12
        set @date2=DATEADD(dd,-1,@date1)
    end

set @hour3=@hour2-4
set @date3=@date2
if @hour1<0
    begin
        set @hour3=12
        set @date3=DATEADD(dd,-1,@date2)
    end


select t0.Item, t0.Color, t0.Size, t0.Sales,
        cast(t0.Sales as float)/cast(t1.Sales as float)  '4-hour diff',
        cast(t0.Sales as float)/cast(t2.Sales as float)  '8-hour diff',
        cast(t0.Sales as float)/cast(t3.Sales as float)  '12-hour diff'
from #tblSales t0
     LEFT JOIN (select Item, Color, Sales from #tblSales where [Date]=@date1  AND [Hour]=@hour1) t1 on t0.Item=t1.Item and t0.Color=t1.Color
     LEFT JOIN (select Item, Color, Sales from #tblSales where [Date]=@date2  AND [Hour]=@hour2) t2 on t0.Item=t2.Item and t0.Color=t2.Color
     LEFT JOIN (select Item, Color, Sales from #tblSales where [Date]=@date3  AND [Hour]=@hour3) t3 on t0.Item=t3.Item and t0.Color=t3.Color
where t0.[Date]=@date0
      AND t0.[Hour]=@hour0


drop table #tblSales