逗号分隔列中可用的价值计数

发布于 2025-02-04 20:23:21 字数 555 浏览 2 评论 0 原文

如下所述，我在MySQL中有一个表：

create table test (
id int,
page_id varchar(2),
name varchar(255));

insert into test values 
(1,'P1','a,b,c,d,e'),
(1,'P2','f,h,z,a,c'),
(1,'P3','x,yz,g,c'),
(2,'P1','a,z'),
(2,'P2','a,c,v,b');

我需要计算不同页面中可用的值的出现。例如，对于

ID 1的“ A”计数为2 ，因为它在Page P1中可用，并且 P2。
ID 1的“ B”计数为1 ，因为它仅在
中可用 Page P1。
ID 1的“ C”计数为3 ，因为它仅在 Page P1，P2和P3

对于ID 1的ID 1和56,000的页数约为2,00,000，

任何人都可以帮助我如何计算页面中ID中“名称”的发生。

原文

I have a table in mysql as mentioned below :

create table test (
id int,
page_id varchar(2),
name varchar(255));

insert into test values 
(1,'P1','a,b,c,d,e'),
(1,'P2','f,h,z,a,c'),
(1,'P3','x,yz,g,c'),
(2,'P1','a,z'),
(2,'P2','a,c,v,b');

I need to count the occurrence of the values available in different pages.
For e.g.

count of "a" is 2 for id 1 because it is available in page P1 and
P2.
count of "b" is 1 for id 1 because it is only available in
page P1.
count of "c" is 3 for id 1 because it is only available in
page P1,P2 and P3

The number of pages are approximately 2,00,000 for id 1 and 56,000 for id 2

Can anyone help me how to count the occurrence of "name" in id for the page.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

彩虹直至黑白 2025-02-11 20:23:21

我的第一个建议是修复数据库设计，而从不存储逗号分离的值。

一种方法是首先您需要将 name 列转换为行。为此，创建一个具有名称列的最大字符长度的数字表。

create table numbers (
nr int );

insert into numbers values (1),(2), (3),(4), (5),(6);

您的数据示例：

create table test (
id int,
page_id varchar(2),
name varchar(255));

insert into test values 
(1,'P1','a,b,c,d,e'),
(1,'P2','f,h,z,a,c'),
(1,'P3','x,y,z,g,c'),
(2,'P1','a,z'),
(2,'P2','a,c,v,b');

下面查询将最终将名称列返回行

select id,
       page_id,
       SUBSTRING_INDEX(SUBSTRING_INDEX(test.name, ',', numbers.nr), ',', -1) as name
from numbers 
inner join test on CHAR_LENGTH(test.name)-CHAR_LENGTH(REPLACE(test.name, ',', ''))>=numbers.nr-1
group by id,page_id,SUBSTRING_INDEX(SUBSTRING_INDEX(test.name, ',', numbers.nr), ',', -1)
order by id asc;

，以便您的预期结果，应用外部查询计数：

select id,name,count(distinct page_id,name) as page_count
from (
       select id,
       page_id,
       SUBSTRING_INDEX(SUBSTRING_INDEX(test.name, ',', numbers.nr), ',', -1) as name
from numbers 
inner join test on CHAR_LENGTH(test.name)-CHAR_LENGTH(REPLACE(test.name, ',', ''))>=numbers.nr-1
group by id,page_id,SUBSTRING_INDEX(SUBSTRING_INDEX(test.name, ',', numbers.nr), ',', -1)
     ) as t1
group by id,name
order by id asc;

https://dbfiddle.uk/?rdbms=mysql_8.0& fiddle = a5dc594b23acc3acc3acc3acc3298ca2c5a103e1103e0fae

注释。对于 count（不同的page_id，name）两列都应具有相同的数据类型

倒置：

sql拆分值

在SQL

First suggestion from me is fixing database design and never store comma separated values.

One way of doing this is first you need to transform the name column into rows. To do so, create a number table with the maximum character length of name column.

create table numbers (
nr int );

insert into numbers values (1),(2), (3),(4), (5),(6);

Your data example:

create table test (
id int,
page_id varchar(2),
name varchar(255));

insert into test values 
(1,'P1','a,b,c,d,e'),
(1,'P2','f,h,z,a,c'),
(1,'P3','x,y,z,g,c'),
(2,'P1','a,z'),
(2,'P2','a,c,v,b');

Below query will return name column into rows

select id,
       page_id,
       SUBSTRING_INDEX(SUBSTRING_INDEX(test.name, ',', numbers.nr), ',', -1) as name
from numbers 
inner join test on CHAR_LENGTH(test.name)-CHAR_LENGTH(REPLACE(test.name, ',', ''))>=numbers.nr-1
group by id,page_id,SUBSTRING_INDEX(SUBSTRING_INDEX(test.name, ',', numbers.nr), ',', -1)
order by id asc;

Finally for your expected result, apply an outer query counting:

select id,name,count(distinct page_id,name) as page_count
from (
       select id,
       page_id,
       SUBSTRING_INDEX(SUBSTRING_INDEX(test.name, ',', numbers.nr), ',', -1) as name
from numbers 
inner join test on CHAR_LENGTH(test.name)-CHAR_LENGTH(REPLACE(test.name, ',', ''))>=numbers.nr-1
group by id,page_id,SUBSTRING_INDEX(SUBSTRING_INDEX(test.name, ',', numbers.nr), ',', -1)
     ) as t1
group by id,name
order by id asc;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=a5dc594b23eacc3298ca2c5a103e0fae

Note. For count(distinct page_id,name) both columns should have the same data types