如何对我的“horse”列中的所有值执行这个“mysql”查询这里是我的“horse”列中的查询execture 1值

tzdcorbm 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(319)

如何对我列中的所有值执行“mysql”查询？
这是我的table

Table A
|----------------------|---------------------|------------------|
|          id          |        CL           |     horse        |    
|----------------------|---------------------|------------------|
|           1          |        1er          |     C.Ferland    |
|           2          |        5e           |     Abrivard     |
|           3          |        3e           |     P. Hawas     |
|----------------------|---------------------|------------------|

我希望输出为：

+------------+--------+---------+---------+-----------+
|    horse   | Top_1  | Top_2_3 | TOP_4_5 | TOP_total |
+------------+--------+---------+---------+-----------+
| C. Ferland | 0.1757 |  0.2788 |  0.1892 |    0.6436 |
|  Abrivard  | 0.0394 |  0.1231 |  0.1575 |    0.3199 |
| P. Hawas   | 0.0461 |  0.1263 |  0.1092 |    0.2816 |
+------------+--------+---------+---------+-----------+

目前，我正在我的horse列中运行一个值的查询。而且效果很好。

SELECT horse,
sum(case when `cl` = '1er' then 1 else 0 end)/count(*) as Top_1, 
sum(case when `cl` BETWEEN 2 AND 3 then 1 else 0 end)/count(*) as Top_2_3,
sum(case when `cl` BETWEEN 4 AND 5 then 1 else 0 end)/count(*) as TOP_4_5,
sum(case when `cl` BETWEEN 1 AND 5 then 1 else 0 end)/count(*) as TOP_total
FROM p_mu.cachedate
WHERE horse ="C.Ferland";

如何使这个查询适应我的“horse”列中的所有值。谢谢你的帮助。。

sql mysql window-functions select average

来源：https://stackoverflow.com/questions/62504585/how-to-execute-this-mysql-query-for-all-values-that-are-in-my-column-horse

1条答案

按热度按时间

qzwqbdag1#

您可以使用条件聚合，但所需的结果表明您希望计算整个数据集的平均值，而不仅仅是组中的行。窗口功能非常方便：

select
    horse,
    sum(cl = 1)      / count(*) over() top_1,
    sum(cl in (2,3)) / count(*) over() top_2_3,
    sum(cl in (4,5)) / count(*) over() top_4_5,
    sum(cl <= 5)     / count(*) over() top_1_5
from p_mu.cachedate
group by horse

如果要对给定的马进行筛选，则需要一个派生表：

select *
from (
    select
        horse,
        sum(cl = 1)      / count(*) over() top_1,
        sum(cl in (2,3)) / count(*) over() top_2_3,
        sum(cl in (4,5)) / count(*) over() top_4_5,
        sum(cl <= 5)     / count(*) over() top_1_5
    from p_mu.cachedate
    group by horse
) t
where horse = 'C.Ferland'

这只适用于mysql 8.0。在早期版本中，可以改用子查询：

select
    horse,
    sum(cl = 1)      / x.cnt top_1,
    sum(cl in (2,3)) / x.cnt top_2_3,
    sum(cl in (4,5)) / x.cnt top_4_5,
    sum(cl <= 5)     / x.cnt top_1_5
from p_mu.cachedate
inner join (select count(*) cnt from p_mu.cachedate) x
group by horse

赞(0）回复(0）举报 2021-07-24

我来回答

如何对我的“horse”列中的所有值执行这个“mysql”查询这里是我的“horse”列中的查询execture 1值

1条答案

相关问题

热门标签

最新问答