计算分配给 SQL 列中字符串标记的数值的总值 [英] Calculating total value of Numeric values assigned to string tokens in SQL column

问题描述

这有点复杂，请耐心等待.

This is kind of complicated, so bear with me.

由于我已经弄清楚了基本概念这个问题

SELECT LENGTH(col) - LENGTH(REPLACE(col, 'Y', ''))

非常聪明的解决方案.

问题是，我正在尝试计算字符串标记的实例数，然后将该计数器乘以表示字符串数值的修饰符.哦，我有一个 50 种代币的列表，每个代币都有不同的价值.

Problem is, I'm trying to count the number of instances of a string token, then take that counter and multiply it by a modifier that represents the string's numeric value. Oh, and I've got a list of 50-ish tokens, each with a different value.

因此，取字符串{5}{X}{W}{b/r}{2/u}{pg}"

So, take the string "{5}{X}{W}{b/r}{2/u}{pg}"

查找标记列表及其数值，我们得到:

Looking up the list of tokens and their numeric value, we get this:

{5}   5
{X}   0
{W}   1
{b/r} 1
{2/u} 2
{pg}  1
....  ....

因此，上面字符串的总和值为 5+0+1+1+2+1 = 10

Therefore, the sum value of the string above is 5+0+1+1+2+1 = 10

现在，我真正要寻找的是一种对 TokenValue 表的每一列进行 Join 并执行上述替换令牌获取长度技巧的方法.

Now, what I'm really looking for is to a way to do a Join and perform the aforementioned replace-token-get-length trick for each column of the TokenValue table.

有意义吗?

伪SQL示例:

SELECT StringColumn, TotalTokenValue
???
FROM TableWithString, TokenValueTable

也许这作为自定义函数会更好?

Perhaps this would work better as a custom Function?

编辑

我想我已经完成了一半，但它有点丑.

I think I'm about halfway there, but it's kind of ugly.

SELECT        StringColumn, LEN(StringColumn) AS TotalLen, Token, 
                { fn LENGTH(Token) } AS TokenLength, TokenValue, 
                { fn REPLACE(StringColumn, Token, '') AS Replaced, 
                { fn LENGTH(Replaced) } AS RepLen,
                { TotalLen - RepLen / TokenLength} AS TokenCount }, 
                { TokenCount * TokenValue} CalculatedTokenValue
FROM            StringTable CROSS JOIN
                         TokenTable

然后我需要将其包装在某种 Group By 中并获得 SUM(CalculatedTokenValue)我可以在脑海中想象，在让 SQL 工作时遇到麻烦.

Then I need to wrap that in some kind of Group By and get SUM(CalculatedTokenValue) I can picture it in my head, having trouble getting the SQL to work.

推荐答案

如果您创建这样的视图:

If you create a view like this one :

Create or replace view ColumnsTokens as 
  select StringColumn, Token, TokenValue, 
   (length(StringColumn) - length(replace(StringColumn, token, ''))) / length(Token) TokenCount 
from StringTable 
join TokenTable on replace(StringColumn, Token, '') <> StringColumn ;

它将充当列和标记之间的多对多关系表，我认为您可以轻松编写所需的任何查询.例如，这会给你总分:

that will act as a many-to-many relationship table between columns and tokens, I think you can easily write any query you need. For example,this will give you the total score :

select StringColumn, sum(TokenCount * TokenValue) TotalTokenScore
   from ColumnsTokens 
group by StringColumn ;

(您只缺少没有标记的 StringColumns)

(You're only missing the StringColumns with no tokens)

这篇关于计算分配给 SQL 列中字符串标记的数值的总值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！