基本的Python类(Basic Python Class)

 您从基本聚合查询开始获取会话和颜色的计数： 
 
select sessionNumber, colorName, count(*) as cnt
from GameEvent ge
group by sessionNumber, colorName;
 
 那么你真的希望MySQL支持ANSI窗口功能 - 唯一不支持row_number()主要数据库。 但是，唉，这个功能不可用。 所以，你有三个选择： 
 
 
 
 技巧与字符串聚合。 
 
 
 变量。 
 
 
 复杂的SQL。 
 
 
 以下显示第一种方法： 
 
select sessionNumber,
       substring_index(group_concat(colorName order by cnt desc), ',', 1) as MostCommonColor
from (select sessionNumber, colorName, count(*) as cnt
      from GameEvent ge
      group by sessionNumber, colorName
     ) sc
group by sessionNumber;
 
 请注意，此查询存在限制。 group_concat()的中间结果具有最大长度（由可更改的参数控制）。 另外，如果颜色名称包含逗号，那么您会希望使用不同的字符。 

You start with the basic aggregation query to get the counts for sessions and colors:
 
select sessionNumber, colorName, count(*) as cnt
from GameEvent ge
group by sessionNumber, colorName;
 
Then you really wish that MySQL supported ANSI window functionality -- the only major database that doesn't support row_number(). But, alas, this functionality is not available. So, you have three choices:
 
 
 
Tricks with string aggregation.
 
 
Variables.
 
 
Complex SQL.
 
 
The following shows the first method:
 
select sessionNumber,
       substring_index(group_concat(colorName order by cnt desc), ',', 1) as MostCommonColor
from (select sessionNumber, colorName, count(*) as cnt
      from GameEvent ge
      group by sessionNumber, colorName
     ) sc
group by sessionNumber;
 
Note that there are limitations on this query. The intermediate results from group_concat() have a maximum length (controlled by a parameter that can be changed). Also, if the color names contain commas, then you would want to use a different character.