如何选择字符串的一部分并按其分组？

发布于 2024-09-02 12:09:49 字数 435 浏览 3 评论 0原文

鉴于我有如下数据，如何选择字符串的各个部分并按其分组？

Version  Users
1.1.1    1
1.1.23   3
1.1.45   1
2.1.24   3
2.1.12   1
2.1.45   3
3.1.10   1
3.1.23   3

我想要的是总结使用版本 1.1.x、2.2.x 和 3.3.x 等的用户，但我不确定如何对 select 语句中的部分字符串进行分组。

编辑数据应该返回的内容是这样的：

Version  Users
1.1.XX   5
2.1.XX   7
3.1.XX   4

版本数量无限可变，有些采用这种格式（主要、次要、构建），有些只是主要、次要，有些只是主要，我唯一一次想要“滚动” up”版本是指有构建时。

原文

Given I have data like the following, how can I select and group by portions of a string?

Version  Users
1.1.1    1
1.1.23   3
1.1.45   1
2.1.24   3
2.1.12   1
2.1.45   3
3.1.10   1
3.1.23   3

What I want is to sum up the users using version 1.1.x and 2.2.x and 3.3.x etc, but I'm not sure how I can group on a partial string in a select statement.

edit
What the data should return like is this:

Version  Users
1.1.XX   5
2.1.XX   7
3.1.XX   4

There is an infinite variable number of versions, some are in this format (major, minor, build) some are just major, minor and some are just major, the only time I want to "roll up" the versions is when there is a build.

分享到QQ

分享到微博

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

发布评论

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

你对谁都笑 2024-09-09 12:09:49

select rtrim(Version, '0123456789') ||'XX', sum(users) 
from Table
group by rtrim(Version, '0123456789')

select rtrim(Version, '0123456789') ||'XX', sum(users) 
from Table
group by rtrim(Version, '0123456789')

回复收藏 0 原文

肩上的翅膀 2024-09-09 12:09:49

您没有指定按第一部分分组后想要看到的内容，也没有指定每个小节的长度。假设您想要计算用户数，并且每个部分不超过一个字符（即没有 10.1.xxx 也没有 10.10.xxx）：

Select substring(Version, 1, 3), Count(*)
From Table
Group By substring(Version, 1, 3)

You did not specify what you want to see after you group by the first portion nor any specification on the length of each subsection. Supposing that you want the count of users and that each section is no more than a single character (i.e., there is no 10.1.xxx nor 10.10.xxx):

Select substring(Version, 1, 3), Count(*)
From Table
Group By substring(Version, 1, 3)

回复收藏 0 原文

孤凫 2024-09-09 12:09:49

Postgres substring（来自模式的字符串） 函数还支持正则表达式。这使得对于复杂字符串也可以轻松解决此问题，其中具有固定开始和长度的 rtrim 或 substring 不起作用。

示例（substring(string,pattern) 是简写，from 可以省略）：

WITH x ("Version", "Users") AS (
select '1.1.1', 1 union all
select '1.1.23', 3 union all
select '1.1.45', 1 union all
select '2.1.24', 3 union all
select '2.1.12', 1 union all
select '2.1.45', 3 union all
select '3.1.10', 1 union all
select '3.1.23', 3)
select
  substring(x."Version", '([0-9]*\.[0-9]*)\.[0-9]*') || '.XX' as "Version",
  sum("Users") as "Users"
from x
group by
  substring(x."Version", '([0-9]*\.[0-9]*)\.[0-9]*');

请注意，我们使用括号子表达式仅返回匹配的一部分，并且示例中省略了 is not null。结果是：

Version Users
1.1.XX  5
2.1.XX  7
3.1.XX  4

来自文档：

有两个参数的 substring 函数，substring(string from
模式），提供与 POSIX 匹配的子字符串的提取
正则表达式模式。如果没有匹配则返回null，
否则是与模式匹配的文本部分。但如果
模式包含任何括号，匹配的文本部分
第一个带括号的子表达式（左括号的子表达式
首先）被返回。您可以将整个括号括起来
表达式，如果你想在其中使用括号而不触发
这个例外。如果您需要在模式之前添加括号
要提取的子表达式，请参阅非捕获括号
如下所述。

注意：上面省略了处理(major,minor)或only(major)形式的版本的逻辑。您还可以轻松地对这些进行分组，例如通过将正则表达式更改为 ([0-9]*|[0-9]*\.[0-9]*)，但我不知道如何这是有道理的。例如，如果您有“1”、“1.2”、“1.2.3”，则仅按专业分组会更明智：“1”可以表示任何内容，从“1.0.0”到“1.2” .4'，但也可能表示“1.2.3”。

The Postgres substring(string from pattern) function also supports regular expressions. This makes it easy to solve this problem also for complex strings where the rtrim or substring with fixed start and length would not work.

Example (substring(string, pattern) is a shorthand, the from can be omitted):

WITH x ("Version", "Users") AS (
select '1.1.1', 1 union all
select '1.1.23', 3 union all
select '1.1.45', 1 union all
select '2.1.24', 3 union all
select '2.1.12', 1 union all
select '2.1.45', 3 union all
select '3.1.10', 1 union all
select '3.1.23', 3)
select
  substring(x."Version", '([0-9]*\.[0-9]*)\.[0-9]*') || '.XX' as "Version",
  sum("Users") as "Users"
from x
group by
  substring(x."Version", '([0-9]*\.[0-9]*)\.[0-9]*');

Note that we use a parenthesized subexpression to return only part of the match, and the is not null is omitted in the example. This results in:

Version Users
1.1.XX  5
2.1.XX  7
3.1.XX  4

From the documentation:

The substring function with two parameters, substring(string from
pattern), provides extraction of a substring that matches a POSIX
regular expression pattern. It returns null if there is no match,
otherwise the portion of the text that matched the pattern. But if the
pattern contains any parentheses, the portion of the text that matched
the first parenthesized subexpression (the one whose left parenthesis
comes first) is returned. You can put parentheses around the whole
expression if you want to use parentheses within it without triggering
this exception. If you need parentheses in the pattern before the
subexpression you want to extract, see the non-capturing parentheses
described below.

Note: the logic for handling versions in the form (major, minor) or only (major) is omitted above. You can easily also group these e.g. by changing the regex to ([0-9]*|[0-9]*\.[0-9]*), but I don't see how that would make sense. E.g. if you have '1', '1.2', '1.2.3', grouping only by major would be more sensible: '1' could mean anything, from '1.0.0' to '1.2.4' but it could also mean '1.2.3'.

回复收藏 0 原文

~没有更多了~