网上商店产品的t-sql查询筛选器

zbdgwd5y 于 2021-07-29 发布在 Java

关注(0)|答案(3)|浏览(364)

我需要创建一个存储过程，它返回产品特性和产品计数。输入参数为：
产品类别。查询必须返回此特定类别及其子类别的数据
所选产品特性-表值参数。如果此参数包含行，则查询必须为具有此特定特征的产品计算每个特征的产品计数。
问题：
数据库包含500000多个产品行，那么就性能而言，最好的解决方案（t-sql查询）是什么？
我试着在下面提出一个查询，但我认为这是丑陋的，实际上没有正确计算产品计数。我需要专业人员的帮助，以尽快做出正确的查询
表脚本和示例数据：

--categories
    create table tCategory(c_id int identity(1,1) primary key, c_name nvarchar(200), c_parent int)
    insert into tCategory(c_name, c_parent) select 'Smartphones', 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

    --products
    create table tProduct(p_id int identity(1,1) primary key, p_name nvarchar(200), c_id int)
    insert into tProduct(p_name, c_id) select '4.7" Apple iPhone 6S 32 gb gold', 2
    insert into tProduct(p_name, c_id) select '4.7" Apple iPhone 6S 32 gb brown', 2
    insert into tProduct(p_name, c_id) select '4.7" Apple iPhone 7 32 gb pink', 3
    insert into tProduct(p_name, c_id) select '4.7" Apple iPhone 7 32 gb brown', 3
    insert into tProduct(p_name, c_id) select '5.5" Apple iPhone 7 Plus 32 gb black', 4
    insert into tProduct(p_name, c_id) select '4.7" Apple iPhone 6S 128 gb pink', 2

    --characteristics type (color, size etc.)
    create table tProductCharItem(pci_id int identity(1,1) primary key, pci_name nvarchar(200))
    insert into tProductCharItem(pci_name) select 'Display'
    insert into tProductCharItem(pci_name) select 'Color'
    insert into tProductCharItem(pci_name) select 'Memory'

    --characteristics value (blue, 50х50 etc.)
    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, 'gold'
    insert into tProductCharItemValue(pci_id, pciv_value) select 2, 'brown'
    insert into tProductCharItemValue(pci_id, pciv_value) select 2, 'pink'
    insert into tProductCharItemValue(pci_id, pciv_value) select 2, 'black'
    insert into tProductCharItemValue(pci_id, pciv_value) select 3, '32 gb'
    insert into tProductCharItemValue(pci_id, pciv_value) select 3, '128 gb'

    --products characteristics
    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

用户未选择任何筛选器时的预期结果：

+--------+---------+----------+------------+----------------+
| pci_id | pciv_id | pci_name | pciv_value | products_count |
+--------+---------+----------+------------+----------------+
|      1 |       1 | Display  | 4.7"       |              5 |
|      2 |       3 | Color    | gold       |              1 |
|      2 |       4 | Color    | brown      |              2 |
|      2 |       5 | Color    | pink       |              2 |
|      2 |       6 | Color    | black      |              1 |
|      3 |       7 | Memory   | 32 gb      |              5 |
|      3 |       8 | Memory   | 128 gb     |              1 |
|      1 |       2 | Display  | 5.5"       |              1 |
+--------+---------+----------+------------+----------------+

用户选择按颜色特征“棕色”过滤时的预期结果

+--------+---------+----------+------------+----------------+
| pci_id | pciv_id | pci_name | pciv_value | products_count |
+--------+---------+----------+------------+----------------+
|      1 |       1 | Display  | 4.7"       |              2 |
|      2 |       3 | Color    | gold       |              0 |
|      2 |       4 | Color    | brown      |              2 |
|      2 |       5 | Color    | pink       |              0 |
|      2 |       6 | Color    | black      |              0 |
|      3 |       7 | Memory   | 32 gb      |              2 |
|      3 |       8 | Memory   | 128 gb     |              0 |
|      1 |       2 | Display  | 5.5"       |              0 |
+--------+---------+----------+------------+----------------+

这是我的尝试（丑陋和不计算产品计数正确）：

CREATE TYPE integer_list_tbltype AS TABLE (n int NOT NULL PRIMARY KEY)

declare @c_id int = 1 --category id
declare @pciv_ids integer_list_tbltype --list of selected filters (products characteristics)

insert into @pciv_ids(n) select 4

;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 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, 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

sql sql-server tsql

来源：https://stackoverflow.com/questions/63147609/t-sql-query-for-online-shop-products-filter

3条答案

按热度按时间

14ifxucb1#

以下是一个查询，用于查找不带筛选器的结果：

select pci.pci_id, pciv.pciv_id, pci.pci_name, pciv.pciv_value,
       count(*)
  from tProductCharItem pci 
  join tProductCharItemValue pciv on pci.pci_id = pciv.pci_id
  join tProductChar pc on pc.pciv_id = pciv.pciv_id
 group by pci.pci_id, pciv.pciv_id, pci.pci_name, pciv.pciv_value
 order by 1, 2

下面是一个查询，用于查找筛选为brown的结果：

select distinct pci.pci_id, pciv.pciv_id, pci.pci_name, pciv.pciv_value,
  sum(case when exists (select null 
                          from tProduct pColor 
                          join tProductChar pcColor 
                            on pColor.p_id = pcColor.p_id 
                          join tProductCharItemValue pcivColor 
                            on pcColor.pciv_id = pcivColor.pciv_id 
                           and pciv_value = 'brown' 
                         where pc.p_id = pColor.p_id)
      then 1 
      else 0 
      end) 
  over (partition by pci.pci_id, pciv.pciv_id, pci.pci_name, pciv.pciv_value)
  from tProductCharItem pci 
  join tProductCharItemValue pciv on pci.pci_id = pciv.pci_id
  join tProductChar pc on pc.pciv_id = pciv.pciv_id
 order by 1, 2

第一个应该很清楚，所以我将解释在第二种情况下会发生什么：我使用的是一个窗口函数/分析函数 sum(...) over (partition by ...) 然后一个鲜明的整体，以达到相同的群体通过。我必须那样做，否则我们会出错的 Cannot perform an aggregate function on an expression containing an aggregate or a subquery. . 带有distinct的样式可能会导致略有不同的结果，但这里的情况并非如此。
在窗口函数中，我使用 case when 为了模拟 count(*) 在给定的条件下。情况正在恶化 exists (select null ...) . 该条件检查给定的行是否是棕色产品的行。所以如果是这样的话，那么exists是真的，那么when是1，它会加起来。
正如@mitz所指出的，“brown”的值是一个常量。您真的要按颜色查询它，然后替换吗 'brown' 使用一个变量，并根据需要将其全部放入tvf或存储过程中。也可以创建这样一个查询，返回所有颜色的值。

赞(0）回复(0）举报 2021-07-29

roejwanj2#

为了避免子查询，我会这样做：

declare @filters table (pciv_id int)
insert into @filters(pciv_id) values (4)

if exists (select * from @filters)
    set @hasFilter=1

;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 
)
, filteredProducts as 
(
    select p.p_id
    from cats
    inner join tProduct p on p.c_id=cats.c_id
    inner join tProductChar pc on pc.p_id=p.p_id
    inner join tProductCharItemValue pcv on pcv.pciv_id=pc.pciv_id
    left join @filters f on f.pciv_id=pc.pciv_id
    where @hasFilter=0 or f.pciv_id is not null
    group by p.p_id
)
select ci.pci_id, pcv.pciv_id, ci.pci_name, pcv.pciv_value, count(p.p_id) products_count
from tProductCharItem ci
inner join tProductCharItemValue pcv on pcv.pci_id=ci.pci_id
left join tProductChar pc on pc.pciv_id=pcv.pciv_id
left join filteredProducts p on p.p_id=pc.p_id
group by ci.pci_id, pcv.pciv_id, ci.pci_name, pcv.pciv_value

赞(0）回复(0）举报 2021-07-29

jqjz2hbq3#

下面是一个tvf，它给出了结果-有或没有颜色参数：

create function tvf_get_products_count(@product_color nvarchar(200))
returns table as
return
with has_attribute_of_given_color as (
select pci.pci_id, pciv.pciv_id, pci.pci_name, pciv.pciv_value, pc.pc_id, pColor.p_id,
       count(pColor.p_id) over (partition by pc.pc_id, pcivColor.pciv_value) as has_attribute_of_given_color 
  from tProductCharItem pci 
  join tProductCharItemValue pciv 
    on pci.pci_id = pciv.pci_id
  join tProductChar pc 
    on pc.pciv_id = pciv.pciv_id
  left join (tProduct pColor 
       join tProductChar pcColor 
         on pColor.p_id = pcColor.p_id 
       join tProductCharItemValue pcivColor 
         on pcColor.pciv_id = pcivColor.pciv_id
       join tProductCharItem pciColor
         on pcivColor.pci_id = pciColor.pci_id
        and pciColor.pci_name = 'Color'
        and pcivColor.pciv_value = isnull(nullif(@product_color, ''), pcivColor.pciv_value))
    on pc.p_id = pColor.p_id
)
select pci_id, pciv_id, pci_name, pciv_value, sum(has_attribute_of_given_color) as products_count 
  from has_attribute_of_given_color
 group by pci_id, pciv_id, pci_name, pciv_value;

像这样使用：

select * from tvf_get_products_count(null) order by 1, 2;
select * from tvf_get_products_count('brown') order by 1, 2;

为了得到这些结果：

从性能的Angular 来看，这可能是最有前途的。必须两次连接所有属性-与我的另一个答案不同，这是通过外部连接实现的，外部连接仅对颜色属性进行过滤，而不是使用子查询。然后将结果再次分组到所需级别。

赞(0）回复(0）举报 2021-07-29

我来回答

网上商店产品的t-sql查询筛选器

3条答案

相关问题

热门标签

最新问答