英文:
How to filter rows based on splitting a text column and showing other rows that have share at least one chunk?
问题
| 项目 | 标签 |
|---|---|
| 衬衫 | 标签，标签1，标签2，标签3 |
| 外套 | 标签，标签7，标签8 |
| 鞋子 | 标签1，标签2，标签5 |
| 夹克 | 标签4，标签5 |
我需要遍历每一行并选择至少有一个相同标签的行。
例如，在“衬衫”上，我需要筛选具有标签、标签1、标签2或标签3的行。所以“外套”和“鞋子”是我想要的结果。
如果我选择“外套”，只有“衬衫”会被返回。不管“外套”是否也被返回。
如何以高效的方式做到这一点？
目前我的“标签”列使用字符串类型，但我愿意使用不同的类型/结构。
谢谢。
英文:
| item | tags |
|---|---|
| shirt | tag, tag1, tag2, tag3 |
| coat | tag, tag7, tag8 |
| shoes | tag1, tag2, tag5 |
| jacket | tag4, tag5 |
I need to iterate on each row and select rows that share at least one of these tags.
For example, on "shirt", I need to filter on rows that have tag, or tag1, or tag2, or tag3. "coat" and "shoes" are the result I want.
If I take "coat", only "shirt" will be returned. It does not matter if "coat" is also returned.
How can I do that in a efficient way ?
Currently my "tags" column uses a string type, but I'm open to use a different type / structure.
Thank you.
答案1
得分: 1
你可以使用split()函数将字符串拆分为行：
select *
from mytable c
CROSS JOIN UNNEST(split(tags,'','')) AS tag_id
where tag_id = 'tag' or tag_id = 'tag1' or tag_id = 'tag2' or tag_id = 'tag3';
要根据特定项而不是标签进行筛选，则：
select distinct s.item
from mytable t
CROSS JOIN UNNEST(split(tags)) tag_id
INNER JOIN (
select *
from mytable t
CROSS JOIN UNNEST(split(tags)) AS tag
) AS s on s.tag = tag_id
where t.item = 'shirt';
英文:
You can do it using split() to split string into rows :
select *
from mytable c
CROSS JOIN UNNEST(split(tags,',')) AS tag_id
where tag_id = 'tag' or tag_id = 'tag1' or tag_id = 'tag2' or tag_id = 'tag3'
To filter by a specific item instead of tags then :
select distinct s.item
from mytable t
CROSS JOIN UNNEST(split(tags)) tag_id
INNER JOIN (
select *
from mytable t
CROSS JOIN UNNEST(split(tags)) AS tag
) AS s on s.tag = tag_id
where t.item = 'shirt'
答案2
得分: 1
以下是翻译好的部分：
假设您可以使用数组来表示您的标签，您可以按照以下步骤解决这个问题：
- 根据您的项目值从表中筛选标签
- 将标签展开以与您筛选的数组相关联
- 使用
EXISTS搜索至少有一个 coat 标签的项目
SELECT item
FROM tab t1
WHERE EXISTS (SELECT 1
FROM tab t2
CROSS JOIN UNNEST(t2.tags) AS tag
WHERE t2.item = <your_chosen_item>
AND tag IN t1.tags)
英文:
Assuming you can use arrays for your tags, you can approach this problem with the following steps:
- filter tags from the table according to your item value
- unnest tags relative to your filtered array
- search which items have at least one coat tag, using
EXISTS
SELECT item
FROM tab t1
WHERE EXISTS (SELECT 1
FROM tab t2
CROSS JOIN UNNEST(t2.tags) AS tag
WHERE t2.item = <your_chosen_item>
AND tag IN t1.tags)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论