Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Фильтр товаров / 3 сообщений из 3, страница 1 из 1
28.07.2020, 21:11
    #39984748
Vsevolod V
Vsevolod V
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Фильтр товаров
Помогите, пожалуйста, с запросом.
Необходимо сформировать источник данных для фильтра по характеристикам товара в интернет магазине с 500 000+ товаров.
Напротив каждой характеристики должно быть количество товаров в магазине с этой характеристикой, в том числе 0 товаров.
При этом после фильтрации по какой-либо характеристике количество должно пересчитываться с учетом уже выбранных пользователем в фильтре характеристик ( пример ).

 
тестовые данные
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
--категории
create table tCategory(c_id int identity(1,1) primary key, c_name nvarchar(50), c_parent int)
insert into tCategory(c_name, c_parent) select 'Телефоны', null
insert into tCategory(c_name, c_parent) select 'iPhone 6S', 1
insert into tCategory(c_name, c_parent) select 'iPhone 7', 1
insert into tCategory(c_name, c_parent) select 'iPhone 7 Plus', 1

--товары
create table tProduct(p_id int identity(1,1) primary key, p_name nvarchar(50), c_id int)
insert into tProduct(p_name, c_id) select '4.7" Смартфон Apple iPhone 6S 32 ГБ золотистый', 2
insert into tProduct(p_name, c_id) select '4.7" Смартфон Apple iPhone 6S 32 ГБ серебристый', 2
insert into tProduct(p_name, c_id) select '4.7" Смартфон Apple iPhone 7 32 ГБ розовый', 3
insert into tProduct(p_name, c_id) select '4.7" Смартфон Apple iPhone 7 32 ГБ серебристый', 3
insert into tProduct(p_name, c_id) select '5.5" Смартфон Apple iPhone 7 Plus 32 Гб черный матовый', 4
insert into tProduct(p_name, c_id) select '4.7" Смартфон Apple iPhone 6S 128 ГБ розовый', 2

--типы характеристик товара (цвет, размер и т.п.)
create table tProductCharItem(pci_id int identity(1,1) primary key, pci_name nvarchar(50))
insert into tProductCharItem(pci_name) select 'Размер экрана'
insert into tProductCharItem(pci_name) select 'Цвет'
insert into tProductCharItem(pci_name) select 'Память'

--значения характеристик товара (синий, 50х50 и т.п.)
create table tProductCharItemValue(pciv_id int identity(1,1) primary key, pci_id int, pciv_value nvarchar(50))
insert into tProductCharItemValue(pci_id, pciv_value) select 1, '4.7"'
insert into tProductCharItemValue(pci_id, pciv_value) select 1, '5.5"'
insert into tProductCharItemValue(pci_id, pciv_value) select 2, 'золотистый'
insert into tProductCharItemValue(pci_id, pciv_value) select 2, 'серебристый'
insert into tProductCharItemValue(pci_id, pciv_value) select 2, 'розовый'
insert into tProductCharItemValue(pci_id, pciv_value) select 2, 'черный матовый'
insert into tProductCharItemValue(pci_id, pciv_value) select 3, '32 Гб'
insert into tProductCharItemValue(pci_id, pciv_value) select 3, '128 ГБ'

--набор характеристик товара
create table tProductChar(pc_id int identity(1,1) primary key, p_id int, pciv_id int)
insert into tProductChar(p_id, pciv_id) select 1, 1
insert into tProductChar(p_id, pciv_id) select 1, 7
insert into tProductChar(p_id, pciv_id) select 1, 3
insert into tProductChar(p_id, pciv_id) select 2, 1
insert into tProductChar(p_id, pciv_id) select 2, 4
insert into tProductChar(p_id, pciv_id) select 2, 7
insert into tProductChar(p_id, pciv_id) select 3, 1
insert into tProductChar(p_id, pciv_id) select 3, 5
insert into tProductChar(p_id, pciv_id) select 3, 7
insert into tProductChar(p_id, pciv_id) select 4, 1
insert into tProductChar(p_id, pciv_id) select 4, 4
insert into tProductChar(p_id, pciv_id) select 4, 7
insert into tProductChar(p_id, pciv_id) select 5, 2
insert into tProductChar(p_id, pciv_id) select 5, 6
insert into tProductChar(p_id, pciv_id) select 5, 7
insert into tProductChar(p_id, pciv_id) select 6, 1
insert into tProductChar(p_id, pciv_id) select 6, 5
insert into tProductChar(p_id, pciv_id) select 6, 8





 
мой вариант

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
CREATE TYPE integer_list_tbltype AS TABLE (n int NOT NULL PRIMARY KEY);

declare @c_id int = 1,
	@pciv_ids integer_list_tbltype

	insert into @pciv_ids(n) select 6 --выбранные пользователем фильтры

;with cats as 
(
    select c_id from tCategory where c_id = @c_id
    union all
    select t.c_id from cats 
        inner join tCategory t on cats.c_id = t.c_parent 
),
groupped_pci as (
select distinct p.c_id, pci.pci_id, pciv.pciv_id
from tProductChar pc 
join tProduct p on pc.p_id = p.p_id
join tProductCharItemValue pciv on pc.pciv_id = pciv.pciv_id
join tProductCharItem pci on pciv.pci_id = pci.pci_id
where pci.pci_isInFilter = 1),
products_count as (
	select count(*) cnt, pc.pciv_id
	from tProduct p join tProductChar pc on p.p_id = pc.p_id
	cross apply (select * from tProductChar pc left join @pciv_ids t on pc.pciv_id = t.n where p_id = p.p_id and pc.pciv_id is not null) t
	group by pc.pciv_id
)

select pci.pci_id, pciv.pciv_id, pci.pci_name, pci.pci_order, pci.pci_url, pci.pi_id_group, pci.pci_valueType, pci.pi_id_uiControlType, pciv.pciv_value, pciv.pciv_url,
pc.cnt products_count
from groupped_pci
join cats on cats.c_id = groupped_pci.c_id
join tProductCharItem pci on groupped_pci.pci_id = pci.pci_id
join tProductCharItemValue pciv on groupped_pci.pciv_id = pciv.pciv_id
left join products_count pc on groupped_pci.pciv_id = pc.pciv_id



...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.07.2020, 09:58
    #39984835
Vsevolod V
Vsevolod V
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Фильтр товаров
Мне удалось самостоятельно получить необходимый результат.
Можете, пожалуйста, оценить решение и дать рекомендации относительно возможностей повышения скорости работы запроса?

 
правильно работающий вариант
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
declare @c_id int = 1
declare @pciv_ids integer_list_tbltype

insert into @pciv_ids(n) select 5

;with cats as 
(
    select c_id from tCategory where c_id = @c_id
    union all
    select t.c_id from cats 
        inner join tCategory t on cats.c_id = t.c_parent 
),
groupped_pci as (
select distinct p.c_id, pci.pci_id, pciv.pciv_id
from tProductChar pc 
join tProduct p on pc.p_id = p.p_id
join tProductCharItemValue pciv on pc.pciv_id = pciv.pciv_id
join tProductCharItem pci on pciv.pci_id = pci.pci_id),
products_count as (
	select count(distinct p.p_id) cnt, pc.pciv_id
	from tProduct p join tProductChar pc on p.p_id = pc.p_id
	cross apply (select * from @pciv_ids t left join tProductChar pc on pc.pciv_id = t.n where p_id = p.p_id and pc.pciv_id is not null) t
	group by pc.pciv_id
)

select distinct pci.pci_id, pciv.pciv_id, pci.pci_name, pciv.pciv_value, pc.cnt products_count
from groupped_pci
join cats on cats.c_id = groupped_pci.c_id
join tProductCharItem pci on groupped_pci.pci_id = pci.pci_id
join tProductCharItemValue pciv on groupped_pci.pciv_id = pciv.pciv_id
left join products_count pc on groupped_pci.pciv_id = pc.pciv_id

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.07.2020, 11:19
    #39984864
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Фильтр товаров
1. Быстро не будет. Совсем.

2. Имхо, можно упростить
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
declare @cats table (c_id int primary key);
insert into @cats
    select c_id from tCategory where c_id = @c_id
    union all
    select t.c_id from cats 
        inner join tCategory t on cats.c_id = t.c_parent;

with products_count as
(
    select count(distinct pc.p_id) cnt, pc.pciv_id
    from
     @pciv_ids t join
     tProductChar pc on pc.pciv_id = t.n join
     tProduct p on p.p_id = pc.p_id join
     @cats c on p.c_id = c.c_id
    group by pc.pciv_id
),
products as
(
 select distinct pci.pci_id, pciv.pciv_id, pci.pci_name, pciv.pciv_value
 from
 tProductChar pc
 join @cats c on c.c_id = p.c_id
 join tProduct p on pc.p_id = p.p_id
 join tProductCharItemValue pciv on pc.pciv_id = pciv.pciv_id
 join tProductCharItem pci on pciv.pci_id = pci.pci_id
)
select
 p.pci_id, p.pciv_id, p.pci_name, p.pciv_value, pc.cnt
from
 products p left join
 products_count pc on pc.pciv_id = p.pciv_id;



Еще можно попробовать кое-что материализовать
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
create view products_info
with schemabinding
as
select
 pc.c_id, pci.pci_id, pciv.pciv_id, pci.pci_name, pciv.pciv_value,
 count_big(*) as cnt
from
 tProductChar pc
 join tProduct p on pc.p_id = p.p_id
 join tProductCharItemValue pciv on pc.pciv_id = pciv.pciv_id
 join tProductCharItem pci on pciv.pci_id = pci.pci_id
group by
 pc.c_id, pci.pci_id, pciv.pciv_id, pci.pci_name, pciv.pciv_value;
go

create unique clustered index CIX_products_info on products_info (c_id, pci_id, pciv_id, pci_name, pciv_value);
go


Тогда CTE products будет выглядеть так
Код: sql
1.
2.
3.
4.
5.
6.
7.
products as
(
 select distinct p.pci_id, p.pciv_id, p.pci_name, p.pciv_value
 from
  @cats c
  join products_info p on p.c_id = c.c_id
)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Фильтр товаров / 3 сообщений из 3, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 411ms
Закрыть
start [/forum/topic.php?fid=46&mobile=1&tid=1685830]:
 0ms
get settings:
 10ms
get forum list:
 15ms
check forum access:
 5ms
check topic access:
 5ms
track hit:
 49ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 47ms
get tp. blocked users:
 2ms
others: 262ms
total:  411ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]