Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 помогите с запросом/логикой  [new]
Serg58
Member

Откуда:
Сообщений: 65
Здравствуйте.

Есть 3 таблицы(я кратко их представил, этого должно быть достаточно):

1 таблица - хранятся товары и их id
item_idname
1Яблоко
2Апельсин
3Банан


2 таблица - проданные товары c временем
sale_id item_id sale_date
122015-09-14 08:00:30
232015-09-15 12:40:21
312015-09-16 14:50:32
442015-09-17 04:00:23
552015-09-18 23:13:43


3 таблица - локация, куда продался товар.
sale_idlocation
1RU
2EN
3EN
4RU
5RU


=================================

Из этих данных очень хочется сделать сводную(информативную) таблицу следующего вида:
товар 13.09 14.09 15.09 16.09 17.09 18.09
Яблоко 1/0 0/2 5/3 1/1 4/3 1/2
Апельсин 0/0 3/1 2/6 1/1 4/3 1/2
Банан 1/2 2/2 4/7 3/1 4/3 1/2

То есть 1 столбец - это товары из 1 таблицы
все остальные столбцы - это последние ~20 дней, в ячейках которых через дробь отражается кол-во проданного в RU/EN.


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

Заранее спасибо ;)
18 сен 15, 08:35    [18163870]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом/логикой  [new]
товары c времен
Guest
Serg58,

location, если это все что есть в 3й таблице, можно и во вторую втянуть. но это так, лирика.

пивот (превращать строки в колонки) и "через черточку" - я бы делал как раз на php.
данные для этого нужно получить через group by

item_id, sale_date, count(case when location=ru then 1 end) sale_ru, ..

останется только один нюанс: колонку наименований так заполнить/обрабатывать чтобы со всеми датами она дружила (не каждый же день каждый товар продается).

если хочется все через sql (и понятно как из php разбирать вариативное число колонок), то pivot
через черточку все равно не надо на sql делать, надо сделать две отдельные колонки.
как отображать - вопрос не к БД.
18 сен 15, 08:52    [18163924]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом/логикой  [new]
Serg58
Member

Откуда:
Сообщений: 65
товары c времен
location, если это все что есть в 3й таблице, можно и во вторую втянуть. но это так, лирика.

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

товары c времен
если хочется все через sql (и понятно как из php разбирать вариативное число колонок), то pivot
через черточку все равно не надо на sql делать, надо сделать две отдельные колонки.

тоже предполагал, что в две отдельные колонки загонять значения для одного дня, а уже в php представлять их в одной.

Почитаю про pivot, ранее не сталкивался.

товары c времен
как отображать - вопрос не к БД.

это понятно ;) спасибо
18 сен 15, 09:12    [18163992]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом/логикой  [new]
Добрый Э - Эх
Guest
Serg58,

PIVOT + dSQL
18 сен 15, 09:20    [18164007]     Ответить | Цитировать Сообщить модератору
 Re: помогите с запросом/логикой  [new]
zero_air
Member

Откуда:
Сообщений: 53
DECLARE @prod table (item_id int, name varchar(20))
INSERT into @prod
VALUES
(1, 'Яблоко'),
(2, 'Апельсин'),
(3, 'Банан'),
(4, 'Пэрсик'),
(5, 'Вишня')

DECLARE @sales table (sale_id int, item_id int, sale_date datetime)
INSERT into @sales
VALUES
(1, 2, convert(datetime, '2015-09-14 08:00:30', 110)),
(2, 3, convert(datetime, '2015-09-15 12:40:21', 110)),
(3, 1, convert(datetime, '2015-09-16 14:50:32', 110)),
(4, 4, convert(datetime, '2015-09-17 04:00:23', 110)),
(5, 5, convert(datetime, '2015-09-18 23:13:43', 110)),
(6, 5, convert(datetime, '2015-09-18 23:13:43', 110))

DECLARE @loc table (sale_id int, location varchar(20))
INSERT into @loc
VALUES
(1, 'RU'),
(2, 'EN'),
(3, 'EN'),
(4, 'RU'),
(5, 'RU'),
(6, 'RU');

IF OBJECT_ID('tempdb..#t1') IS NOT NULL
    DROP TABLE #t1

CREATE TABLE #t1
(
	date_s       varchar(5),
	name_p       varchar(20),
	sales_ru     int,
	sales_en     int
) 

INSERT into #t1
SELECT convert(varchar(5), s.sale_date, 104) AS date_s,
       p.name,
       count(CASE WHEN l.location = 'RU' THEN 1 END) AS sales_ru,
       count(CASE WHEN l.location = 'EN' THEN 1 END) AS sales_en
FROM   @sales           AS s
       left JOIN @prod  AS p
            ON  p.item_id = s.item_id
       left JOIN @loc   AS l
            ON  l.sale_id = s.sale_id
GROUP BY
       p.name,
       s.sale_date	

--###########красиво завернуть в функцию##### START
DECLARE @calendar table (dates datetime)
DECLARE @first_day datetime
DECLARE @last_day datetime
SET @first_day= getdate()-20
SET @last_day= getdate()  

While @first_day< @last_day
BEGIN
	set @first_day=@first_day+1
	INSERT into @calendar
	select @first_day
END

SET @first_day= getdate()-20

DECLARE @days nvarchar(max)
SET @days = (
        SELECT  '[' + convert(varchar(5), @first_day, 104) + ']' + (
                   SELECT  ',[' + convert(varchar(5), t2.dates, 104) + ']'
                   FROM   @calendar t2 order by  t2.dates for xml path('') 
               )
)

--###########красиво завернуть в функцию##### END

DECLARE @sql nvarchar(4000)
SET @sql = ' select name_p,' + @days + 
    '
   from  (select  date_s ,name_p ,sales_ru ,sales_en from #t1 ) x
   PIVOT (sum(sales_ru) FOR date_s IN ( ' +
    @days
    + ' ) ) as pvt '

  
EXECUTE sp_executesql @sql

SET @sql = ' select name_p,' + @days + 
    '
   from  (select  date_s ,name_p ,sales_ru ,sales_en from #t1 ) x
   PIVOT (sum(sales_en) FOR date_s IN ( ' +
    @days
    + ' ) ) as pvt '

EXECUTE sp_executesql @sql


Как то, так.
Хотя... чую за такое решение меня побьют ногами :)
18 сен 15, 16:55    [18166917]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить