Помогите, пожалуйста, с запросом.
Необходимо сформировать источник данных для фильтра по характеристикам товара в интернет магазине с 500 000+ товаров.
Напротив каждой характеристики должно быть количество товаров в магазине с этой характеристикой, в том числе 0 товаров.
При этом после фильтрации по какой-либо характеристике количество должно пересчитываться с учетом уже выбранных пользователем в фильтре характеристик ( пример ).
тестовые данные
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. вызывает большие сомнения на предмет оптимальности относительно производительности
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
|
