如何在MySQL中从逗号分隔字符串值中计算中间值？

Question

问题是如何计算分布在表中的值的中值。对于相同的uid，表列中有多个值，需要为每个uid找到这些值的中值。为了做到这一点，我首先将这些值连接起来，然后尝试针对每个uid找到分组值之间的中值。在组连接之后，我将这些值放入逗号分隔的列表中。

现在，我需要从下表"text_responded_in_hour“中给出的逗号分隔值中找到中位数。

uId |text_responded_in_hour 
----|-----------------------
176 |70,660,70
177 |102      
194 |102,102  

我试过谷歌，但没有找到任何相关的解决方案。

Answer 1

以下用于计算中间值的代码可以用于偶数和奇数的值。这段代码的目的是:)

SELECT  
uId,    
date,    
(SUBSTRING_INDEX(
        SUBSTRING_INDEX(
            GROUP_CONCAT(responded_text_time_in_hour ORDER BY responded_text_time_in_hour),
            ',',
            ((
                ROUND(
                    LENGTH(GROUP_CONCAT(responded_text_time_in_hour)) - 
                    LENGTH(
                        REPLACE(
                            GROUP_CONCAT(responded_text_time_in_hour),
                            ',',
                            ''
                        )
                    )
                ) / 2) + 1
            )),
            ',',
            -1
        ) +
        SUBSTRING_INDEX( 
            SUBSTRING_INDEX( 
                GROUP_CONCAT(responded_text_time_in_hour ORDER BY responded_text_time_in_hour), ',', (COUNT(*)/2) )
                , ',', -1))/2 as median

FROM outTable
WHERE
    (responded_text_time_in_hour>0 AND responded_text_time_in_hour <=3600)
GROUP BY 1,2

Answer 2

这样做是不可行的。逗号分隔列表通常在数据库中是一个非常糟糕的主意，因为这样的问题。

这是可能的，如果不是快速或灵活的话。

下面的方法是从1到1000之间生成一个数字列表(或者比逗号数多一个)。只有当逗号分隔值的最大值小于1000时，它才能工作。可以很容易地扩展，以应付更大的数字，但将变得更低的效率，因为它是扩大。

然后使用该范围的数字从逗号分隔的列表中提取一个值。

一旦完成，就可以使用AVG函数。

SELECT uID, 
        AVG(individual_responded_in_hour)
FROM
(
    SELECT uID,
            CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(text_responded_in_hour, ',', (hundreds.aCnt * 100 + tens.aCnt * 10 + units.aCnt + 1)), ',', -1) AS SIGNED) AS individual_responded_in_hour
    FROM some_table
    CROSS JOIN (SELECT 1 AS aCnt UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) units
    CROSS JOIN (SELECT 1 AS aCnt UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) tens
    CROSS JOIN (SELECT 1 AS aCnt UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10) hundreds
    WHERE (hundreds.aCnt * 100 + tens.aCnt * 10 + units.aCnt) <= (LENGTH(text_responded_in_hour) - LENGTH((REPLACE(text_responded_in_hour, ',', '')))
) sub0
GROUP BY uID

可以使用自定义编写的MySQL函数来完成这一任务，这可能会更有效。

但无论哪种方式，我都认为这样做的复杂性更好的理由是不要将值存储在逗号分隔的列表中。

Answer 3

您确实应该使用像python这样的编程语言来做到这一点。您不能轻松地使用MySQL，您可以使用postgres、MSSQL或任何其他dbms来实现这一点。在MySQL中，如果您知道有多少值，就可以通过使用json函数来破解它。但这看起来是错误的(适用于MySQL 5.7.9+)：

insert into yourtable
select uId as id, concat('[', text_responded_in_hour, ']') as jsoncol
from startingtable;

select ID, AVG(val)
(
select id, jsoncol->'$[0]' as val from yourtable
union all select id, jsoncol->'$[1]' from yourtable
union all select id, jsoncol->'$[2]' from yourtable
-- as many times as needed
) as a
group by ID