计算逗号分隔列中关键字的出现次数？ [英] Count number of occurrences of keyword in comma separated column?

问题描述

我有一个列存储如下数据：

I have a column which stores data like this:

产品：

product1，product2，product5

product5 ，product7

product1

Product:
product1,product2,product5
product5,product7
product1

我想做的是计算product1，product2等的出现次数，但记录包含多个产品我希望它重复计算它们。

What I would like to do is count the number of occurrences there are of product1, product2, etc. but where the record contains multiple products I want it to double count them.

所以对于上面的例子，总计将是：

product1：2

product2：1

product5：2

product7：1

So for the above example the totals would be:
product1: 2
product2: 1
product5: 2
product7: 1

我如何实现？

我正在尝试这样的：

    select count(case when prodcolumn like '%product1%' then 'product1' end) from myTable

我也尝试过这样的：

    select new_productvalue, count(new_productvalue) from OpportunityExtensionBase 
    group by new_ProductValue

但是列出了发现的产品的所有不同组合和发现的次数...

But that lists all different combinations of the products which were found and how many times they were found...

这些产品不会改变这么硬的编码是确定...

These products don't change so hard coding it is ok...

编辑：这里是对我有用。

here is what worked for me.

WITH Product_CTE (prod) AS 
(SELECT
  n.q.value('.', 'varchar(50)')
    FROM (SELECT cast('<r>'+replace(new_productvalue, ';', '</r><r>')+'</r>' AS xml) FROM table) AS s(XMLCol)
      CROSS APPLY s.XMLCol.nodes('r') AS n(q)
    WHERE n.q.value('.', 'varchar(50)') <> '')
    SELECT prod, count(*) AS [Num of Opps.] FROM Product_CTE GROUP BY prod

推荐答案

你有一个糟糕的，糟糕的数据结构，但有时必须这样做。

You have a lousy, lousy data structure, but sometimes one must make do with that. You should have a separate table storing each pair product/whatever pair -- that is the relational way.

with prodref as (
      select 'product1' as prod union all
      select 'product2' as prod union all
      select 'product5' as prod union all
      select 'product7' as prod
     )
select p.prod, count(*)
from prodref pr left outer join
     product p
     on ','+p.col+',' like '%,'+pr.prod+',%'
group by p.prod;

这在大表上会很慢。而且，查询不能使用标准索引。但是，它应该工作。如果您可以重组数据，那么您应该。

This will be quite slow on a large table. And, the query cannot make use of standard indexes. But, it should work. If you can restructure the data, then you should.

这篇关于计算逗号分隔列中关键字的出现次数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！