英文:
Calculate percentage from values in a string using SQL
问题
I have a column with progress status
| progress|
| -------- |
|10 of 100 completed|
|20 of 100 completed|
|40 of 200 completed|
|69 of 420 completed|
etc
我不确定我是否正确表达了标题：
我有一个包含进度状态的列，如下所示：
我想知道是否有一种简单的方法来计算每行的百分比，或者我必须使用正则表达式将值分离到两列，然后进行计算？
我考虑类似这样的东西（我会搞砸这个，我道歉，我实际上不知道如何编写SQL）：
regexep (r'^[0-9]+) / regexp (r'[0-9]$)
如果需要代码翻译，可以提供详细的代码部分以便进行翻译。
英文:
I'm not entirely sure I worded the title properly:
I have a column with progress status
| progress|
| -------- |
|10 of 100 completed|
|20 of 100 completed|
|40 of 200 completed|
|69 of 420 completed|
etc
I'm wondering if there's a simple way to calculate the percentage of each row in 1 line? Or do I have to separate the values out with regex to 2 columns and calculate it that way?
I was thinking something like (I'm going to butcher this I apologize, I don't actually know how to write SQL):
regexep (r'^[0-9]+) / regexp (r'[0-9]$)
答案1
得分: 1
在MySQL 8.0+中，您可以使用regexp_substr()在一行中获取结果。
以下是查询：
select
task,
CAST(REGEXP_SUBSTR(progress, '\d+', 1, 1) AS float) / CAST(REGEXP_SUBSTR(progress, '\d+', 1, 2) AS float) as progress_rate
from
task
| task | progress_rate |
|---|---|
("10 of 100 completed") |
0.1 |
("20 of 100 completed") |
0.2 |
("40 of 200 completed") |
0.2 |
("69 of 420 completed") |
0.16428571428571428 |
英文:
In MySQL 8.0+, you can use regexp_substr() to get the result in one line.
Here is the query:
select
task,
CAST(REGEXP_SUBSTR(progress, '\d+', 1, 1) AS float) / CAST(REGEXP_SUBSTR(progress, '\d+', 1, 2) AS float) as progress_rate
from
task
| task | progress_rate |
|---|---|
("10 of 100 completed") |
0.1 |
("20 of 100 completed") |
0.2 |
("40 of 200 completed") |
0.2 |
("69 of 420 completed") |
0.16428571428571428 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论