mysql:计算每个组中的值数
mysql:计算每个组中的值数
提问于 2016-11-30 11:23:53
我有一个疑问：
SELECT
count(session_id_open) as opens,
count(session_id_visit) as visits,
date_intervals_open,
group_concat(date_intervals_visit)
FROM
bla
GROUP BY date_intervals_open我得到了后面的桌子。我需要的是按百分比来查找group_concat中出现的每个值的出现率。因此，基本上，我需要计算每个组中的值数(date_intervals_visit) (data_intervals_open)。
opens visits date_intervals_open group_concat
213 5 day (12-16) evening (17-21),evening (17-21),day (12-16),day (12-16),day (12-16)
113 0 evening (17-21) NULL
11 0 late evening (22-00) NULL
396 12 morning (5-11) morning (5-11),morning (5-11),morning (5-11),morning (5-11),morning (5-11),morning (5-11),morning (5-11),morning (5-11),morning (5-11),morning (5-11),morning (5-11),morning (5-11)
9 0 night (1-4) NULL那大概是我需要得到的桌子。在第一个记录中，晚上有40个，因为“夜晚(17-21)”出现了两次，所有发生的次数为5. 2/5*100=40。
opens visits date_intervals_open evening(17-21) day(12-16) morning (5-11)
213 5 day (12-16) 40 60 0
113 0 evening (17-21) NULL NULL NULL
11 0 late evening (22-00) NULL NULL NULL
396 12 morning (5-11) 0 0 100
9 0 night (1-4) NULLPS:我使用group_concat只是为了形象化我所拥有的价值。我不需要使用它，因为它将是一个额外的努力，以分析它之后。
回答 3
Stack Overflow用户
回答已采纳
发布于 2016-11-30 11:52:45
你本质上需要一个支点，还有一些进一步的计算。我的答案的基础来自于以下关于pivoting records in MySQL的优秀主题。我假设您有一个固定数量的date_intervals_visit值，因为这些值似乎涵盖了一整天，因此我对固定数量的计数使用条件计数方法。我将在示例代码中添加两个类别，您可以将其扩展到涵盖所有date_intervals_visit值。
SELECT
count(session_id_open) as opens,
count(session_id_visit) as visits,
date_intervals_open,
round(sum(if(date_intervals_visit='morning (5-11)',1,0)) / count(session_id_visit) * 100,2) as `morning (5-11)`,
round(sum(if(date_intervals_visit='day (12-16)',1,0)) / count(session_id_visit) * 100,2) as `day (12-16)`
FROM
bla
GROUP BY date_intervals_open如果可以按date_intervals_open值进行0次访问，则需要在表达式中检查0：
if(count(session_id_visit)=0, 0, <above formula>)Stack Overflow用户
发布于 2016-12-01 00:28:04
SELECT
count(session_id_open) as opens,
@visits := count(session_id_visit) as visits,
date_intervals_open,
ROUND(100 * SUM(date_intervals_visit = 'evening(17-21)') / @visits) AS 'evening(17-21)',
ROUND(100 * SUM(date_intervals_visit = 'day (12-16)') / @visits) AS 'day (12-16)',
ROUND(100 * SUM(date_intervals_visit = 'morning (5-11)') / @visits)'morning (5-11)',
FROM
bla
GROUP BY date_intervals_openStack Overflow用户
发布于 2016-11-30 11:34:26
使用这样的函数：
CREATE FUNCTION [dbo].[fn_SplitString](
@InputStr varchar(Max),
@Seperator varchar(10))
RETURNS @OutStrings TABLE (ItemNo int identity(1,1), Item varchar(256))
AS
BEGIN
DECLARE @Str varchar(2000),
@Poz int, @cnt int
--DECLARE @OutStrings TABLE (Item varchar(2000))
SELECT @Poz = CHARINDEX (@Seperator, @InputStr), @cnt = 0
WHILE @Poz > 0 AND @cnt <= 10000
BEGIN
SELECT @Str = SubString(@InputStr, 1, @Poz - 1)
INSERT INTO @OutStrings(Item) VALUES(@Str)
SELECT @InputStr = Right(@Inputstr, Len(@InputStr) - (len(@Str) + len(@Seperator)))
SELECT @Poz = CHARINDEX (@Seperator, @InputStr), @cnt = @cnt + 1
END
IF @InputStr <> ''
BEGIN
INSERT INTO @OutStrings(Item) VALUES(@InputStr)
END
RETURN
END以下列方式：
SELECT opens,
visits,
date_intervals_open,
[evening(17-21)]/[All]*100 AS [evening(17-21)],
[day(12-16)]/[All]*100 AS [day(12-16)],
[morning (5-11)]/[All]*100 AS [morning (5-11)]
FROM
(
SELECT
count(session_id_open) as opens,
count(session_id_visit) as visits,
date_intervals_open,
(SELECT COUNT(Item) FROM [dbo].[fn_SplitString](LTRIM(RTRIM(group_concat(date_intervals_visit))), ',') WHERE item = 'evening(17-21)') AS [evening(17-21)],
(SELECT COUNT(Item) FROM [dbo].[fn_SplitString](LTRIM(RTRIM(group_concat(date_intervals_visit))), ',') WHERE item = 'day(12-16)') AS [day(12-16)],
(SELECT COUNT(Item) FROM [dbo].[fn_SplitString](LTRIM(RTRIM(group_concat(date_intervals_visit))), ',') WHERE item = 'morning (5-11)') AS [morning (5-11)],
(SELECT COUNT(Item) FROM [dbo].[fn_SplitString](LTRIM(RTRIM(group_concat(date_intervals_visit))), ',')) AS [All]
FROM
bla
GROUP BY date_intervals_open
)blabla页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/40887114
复制相关文章
相似问题
腾讯云开发者
