如何执行此"MySQL"查询我的列"horse"中的所有值...此查询在我的"horse"列中显示了1个值， [英] how to execute this "MySQL" query for all values that are in my column "horse" ... here this query execture 1 value in my column "horse"

问题描述

如何执行"MySQL"查询全部列中的值是什么?

how to execute a "MySQL" query for all values that are in my column ?

这是我的桌子

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 |
+------------+--------+---------+---------+-----------+

当前，我正在我的马列中运行此查询以获取一个值.而且效果很好.

Currently, I'm running this query for a value in my horse column. And that works very well.

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";

如何针对我的马"中的所有值调整此查询柱子.谢谢您的帮助.

How to adapt this query for all the values in my "horse" column. Thank you for your help..

推荐答案

您可以使用条件聚合，但是您希望得到的结果表明，您希望计算整个数据集的平均值，而不仅仅是计算组中的行.窗口函数可以方便地实现这一点:

You can use conditional aggregation, but your desired results show that you want to compute the average over the whole dataset, not only over the rows of the group. Window functions come handy for this:

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

如果要过滤给定的马，则需要派生表:

If you want to filter on a given horse, you need a derived table:

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中有效.在早期版本中，您可以改用子查询:

---

This works in MySQL 8.0 only. In earlier versions, you can use a subquery instead:

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

这篇关于如何执行此"MySQL"查询我的列"horse"中的所有值...此查询在我的"horse"列中显示了1个值，的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！