计算在一行中有两列或更多列的值超过某个特定值 [篮球，双十，三双]

Question

我玩的篮球游戏可以将统计数据输出为数据库文件，因此可以根据这些数据计算游戏中未实现的统计指标。到目前为止，我一直没有问题地计算出我想要的统计数据，但现在遇到了一个问题：如何从球员的比赛统计数据中计算出他在整个赛季中完成的两双和/或三双次数。 两双和三双的定义如下： 两双： 在一场比赛中，球员在五个统计指标（得分、篮板、助攻、抢断和盖帽）中至少有两项达到两位数。 三双： 在一场比赛中，球员在五个统计指标（得分、篮板、助攻、抢断和盖帽）中至少有三项达到两位数。 四双（为了澄清而添加）： 在一场比赛中，球员在五个统计指标（得分、篮板、助攻、抢断和盖帽）中至少有四项达到两位数。 "PlayerGameStats"表存储每个玩家参与的游戏统计数据，其结构如下所示：

CREATE TABLE PlayerGameStats AS SELECT * FROM ( VALUES
  ( 1, 1,  1, 'Nuggets',    'Cavaliers',  6,  8,  2, 2,  0 ),
  ( 2, 1,  2, 'Nuggets',     'Clippers', 15,  7,  0, 1,  3 ),
  ( 3, 1,  6, 'Nuggets', 'Trailblazers', 11, 11,  1, 2,  1 ),
  ( 4, 1, 10, 'Nuggets',    'Mavericks',  8, 10,  2, 2, 12 ),
  ( 5, 1, 11, 'Nuggets',       'Knicks', 23, 12,  1, 0,  0 ),
  ( 6, 1, 12, 'Nuggets',         'Jazz',  8,  8, 11, 1,  0 ),
  ( 7, 1, 13, 'Nuggets',         'Suns',  7, 11,  2, 2,  1 ),
  ( 8, 1, 14, 'Nuggets',        'Kings', 10, 15,  0, 3,  1 ),
  ( 9, 1, 15, 'Nuggets',        'Kings',  9,  7,  5, 0,  4 ),
  (10, 1, 17, 'Nuggets',      'Thunder', 13, 10, 10, 1,  0 )
) AS t(id,player_id,seasonday,team,opponent,points,rebounds,assists,steals,blocks);

我想要实现的输出如下：

| player_id |    team | doubleDoubles | tripleDoubles |
|-----------|---------|---------------|---------------|
|         1 | Nuggets |             4 |             1 |

到目前为止，我找到的唯一解决方案太糟糕了，简直让我想吐... ;o) ... 它看起来是这样的：

SELECT 
  player_id,
  team,
  SUM(CASE WHEN(points >= 10 AND rebounds >= 10) OR
               (points >= 10 AND assists  >= 10) OR
               (points >= 10 AND steals   >= 10) 
                THEN 1 
                ELSE 0 
      END) AS doubleDoubles
FROM PlayerGameStats
GROUP BY player_id

...现在你可能在读完这段话之后感到恶心（或者大笑不已）。我甚至没有写出所有需要的代码来获取所有的双倍组合，并且也忽略了三倍双数据的情况，因为这更加荒谬。 有没有更好的方法呢？无论是使用我已经拥有的表结构，还是使用一个新的表结构（我可以编写一个脚本来转换表）。 我可以使用MySQL 5.5或PostgreSQL 9.2。 这是一个带有示例数据和我之前发布的糟糕解决方案的SqlFiddle链接：http://sqlfiddle.com/#!2/af6101/3 请注意，我对四重双打并不太感兴趣（参见上文），因为据我所知，在我玩的游戏中并不存在这种情况，但如果查询能够轻松扩展而无需进行太多重写以适应四重双打，那将是一个加分项。

- user39509

Answer 1

不知道这是否是最好的方法。我首先执行了一个选择操作，以确定一个统计数据是否为两位数，并在是的情况下赋值为1。然后将所有这些相加，以确定每场比赛的两位数总数。从那里开始，只需将所有的双打和三打相加即可。看起来似乎有效。

select a.player_id, 
a.team, 
sum(case when a.doubles = 2 then 1 else 0 end) as doubleDoubles, 
sum(case when a.doubles = 3 then 1 else 0 end) as tripleDoubles
from
(select *, 
(case when points > 9 then 1 else 0 end) +
(case when rebounds > 9 then 1 else 0 end) +
(case when assists > 9 then 1 else 0 end) +
(case when steals > 9 then 1 else 0 end) +
(case when blocks > 9 then 1 else 0  end) as Doubles
from PlayerGameStats) a
group by a.player_id, a.team

- SQLChao

---

嗨，谢谢你的解决方案。我真的很喜欢它。完全符合我的要求，并且可以轻松扩展以包括四双和五双，而不需要太多的编写。暂时将其设为采纳答案。 :) - user39509

我喜欢你的代码，但是你可以通过一些技巧让它更短。不需要使用CASE语句，因为布尔表达式在为真时会评估为1，为假时会评估为0。我已经将它添加到了我的答案中，并向你致以感谢，因为在这里无法在评论中发布完整的SQL代码块。 - Joshua Huber

谢谢，乔舒亚。完全忽视了那个，现在看起来好多了。 - SQLChao

@JoshuaHuber 对，但是这个查询只能在MySQL中使用。使用CASE和SUM/COUNT可以让它在Postgres中也能工作。 - ypercubeᵀᴹ

@ypercube：实际上，在Postgres中，添加布尔值也是可行的。您只需要显式地进行类型转换。但是，使用CASE通常会稍微快一点。我添加了一个演示，还有一些其他小的改进。 - Erwin Brandstetter

Answer 2

尝试一下这个方法（在MySQL 5.5上对我有效）：

SELECT 
  player_id,
  team,
  SUM(
    (   (points   >= 10)
      + (rebounds >= 10)
      + (assists  >= 10)
      + (steals   >= 10)
      + (blocks   >= 10) 
    ) = 2
  ) double_doubles,
  SUM(
    (   (points   >= 10)
      + (rebounds >= 10)
      + (assists  >= 10)
      + (steals   >= 10)
      + (blocks   >= 10) 
    ) = 3
  ) triple_doubles
FROM PlayerGameStats
GROUP BY player_id, team

甚至更简短的方法是，直接从JChao的答案中抄袭代码，但去掉不必要的CASE语句，因为布尔表达式在True和False时分别评估为1和0。

select a.player_id, 
a.team, 
sum(a.doubles = 2) as doubleDoubles, 
sum(a.doubles = 3) as tripleDoubles
from
(select *, 
(points > 9) +
(rebounds > 9) +
(assists > 9) +
(steals > 9) +
(blocks > 9) as Doubles
from PlayerGameStats) a
group by a.player_id, a.team

根据上述代码无法在PostgreSQL中运行的评论，因为它不喜欢进行布尔值相加。我仍然不喜欢CASE语句。在PostgreSQL（9.3）中，可以通过将其转换为int来解决这个问题。

select a.player_id, 
a.team, 
sum((a.doubles = 2)::int) as doubleDoubles, 
sum((a.doubles = 3)::int) as tripleDoubles
from
(select *, 
(points > 9)::int +
(rebounds > 9)::int +
(assists > 9)::int +
(steals > 9)::int +
(blocks > 9)::int as Doubles
from PlayerGameStats) a
group by a.player_id, a.team

- Joshua Huber

---

@ypercube，说得好，谢谢。我刚刚在上面的问题中留言要求澄清这一点。语义学。我相信在曲棍球比赛中，四个进球仍然被认为是“帽子戏法”，但是保龄球比赛中连续四次击倒全部球瓶可能不被视为一个正式的“火鸡”，而是一个“四连”。我对每个游戏的语义学并不是专家。你可以根据情况选择使用=还是>=。 - Joshua Huber

感谢您的解决方案。绝对符合我的需求。我也喜欢您提供的JChao的简化版本。 - user39509

在 PostgreSQL 中，添加布尔值不起作用，请记住这一点。 - Craig Ringer

@CraigRinger - 谢谢你指出这一点。由于我对SQL总体和特别是PostgreSQL还不够熟悉，这对我来说是非常有价值的信息。 :) - user39509

@CraigRinger，感谢你对PostgreSQL的反馈。我在我的解决方案中添加了第三种变体。如果你将布尔值转换为::int，你可以在PostgreSQL中添加布尔值。 - Joshua Huber

是的，你可以通过使用CAST(points > 9 AS int)来使其符合标准，而不是使用::int；我相信PostgreSQL和MySQL都能理解这个。 - Craig Ringer

@CraigRinger 不错，但我不认为MySQL支持 CAST(... AS int)（http://stackoverflow.com/questions/12126991/cast-from-varchar-to-int-mysql）。 MySQL可以使用 CAST(... AS UNSIGNED)，这在此查询中有效，但PostgreSQL则不行。不确定两者都能做到可移植性的通用 CAST。最坏情况下，如果可移植性至关重要，最终可能会被困在CASE中。 - Joshua Huber

Answer 3

这是对问题的另一种看法。 我认为，对于当前的问题，你实际上是在处理透视数据，所以首先要做的是将其转换为非透视数据。不幸的是，PostgreSQL没有提供很好的工具来完成这个任务，所以如果不涉及到在PL/PgSQL中生成动态SQL，我们至少可以做到：

SELECT player_id, seasonday, 'points' AS scoretype, points AS score FROM playergamestats
UNION ALL
SELECT player_id, seasonday, 'rebounds' AS scoretype, rebounds FROM playergamestats
UNION ALL
SELECT player_id, seasonday, 'assists' AS scoretype, assists FROM playergamestats
UNION ALL
SELECT player_id, seasonday, 'steals' AS scoretype, steals FROM playergamestats
UNION ALL
SELECT player_id, seasonday, 'blocks' AS scoretype, blocks FROM playergamestats

这将数据转换成了更加可塑的形式，虽然看起来并不美观。在这里，我假设（player_id，seasonday）足以唯一标识球员，即球员ID在各个队伍中是唯一的。如果不是这样的话，你需要包含足够的其他信息来提供一个唯一的键。 有了这个非枢轴数据，现在可以以有用的方式进行过滤和聚合，比如：

SELECT
  player_id,
  count(CASE WHEN doubles = 2 THEN 1 END) AS doubledoubles,
  count(CASE WHEN doubles = 3 THEN 1 END) AS tripledoubles
FROM (
    SELECT
      player_id, seasonday, count(*) AS doubles
    FROM
    (
        SELECT player_id, seasonday, 'points' AS scoretype, points AS score FROM playergamestats
        UNION ALL
        SELECT player_id, seasonday, 'rebounds' AS scoretype, rebounds FROM playergamestats
        UNION ALL
        SELECT player_id, seasonday, 'assists' AS scoretype, assists FROM playergamestats
        UNION ALL
        SELECT player_id, seasonday, 'steals' AS scoretype, steals FROM playergamestats
        UNION ALL
        SELECT player_id, seasonday, 'blocks' AS scoretype, blocks FROM playergamestats
    ) stats
    WHERE score >= 10
    GROUP BY player_id, seasonday
) doublestats
GROUP BY player_id;

这与漂亮相去甚远，而且可能并不那么快。但它是可维护的，只需要最少的更改来处理新类型的统计数据、新列等。

所以它更像是一种“嘿，你有没有想过”而不是一个严肃的建议。目标是尽可能直接地将SQL建模与问题陈述对应起来，而不是让它很快。

---

这在你使用合理的多值插入和ANSI引用的MySQL导向SQL中变得更加容易。谢谢；很高兴这次没有看到反引号。我只需要改变一下合成键生成就可以了。

- Craig Ringer

---

这差不多是我心目中的想法。 - Colin 't Hart

感谢您发布这个解决方案。根据@Colin'tHart上面的建议，我在实施类似的东西时遇到了一些问题（以前从未做过这样的事情，但似乎对于我将来可能想要计算的其他统计数据非常有用）。有趣的是，有很多方法可以实现我想要的输出。今天肯定学到了很多。 - user39509

要了解更多，请分析查询计划（或MySQL等价物），弄清楚它们都做什么以及怎么做 :) - Craig Ringer

@CraigRinger - 谢谢。好建议。实际上，我已经用所有提供的解决方案做了类似的事情（我使用了SqlFiddles的“查看执行计划”功能）。但是当阅读输出结果时，我肯定需要努力弄清楚它们都是做什么和如何做的部分。=O - user39509

Answer 4

在Postgres中，@Joshua显示的MySQL内容同样适用。可以将Boolean值转换为integer并相加。尽管需要显式地进行转换，但代码非常简洁。

SELECT player_id, team
     , count(doubles = 2 OR NULL) AS doubledoubles
     , count(doubles = 3 OR NULL) AS tripledoubles
FROM  (
   SELECT player_id, team,
          (points   > 9)::int +
          (rebounds > 9)::int +
          (assists  > 9)::int +
          (steals   > 9)::int +
          (blocks   > 9)::int AS doubles
   FROM playergamestats
   ) a
GROUP  BY 1, 2;

同时还利用了一种更短的方法来计算外部SELECT中的重复项。
有关详细信息，请参阅相关答案。
然而，CASE虽然更冗长，但通常会稍微快一点。如果这很重要的话，它也更具可移植性。

SELECT player_id, team
     , count(doubles = 2 OR NULL) AS doubledoubles
     , count(doubles = 3 OR NULL) AS tripledoubles
FROM  (
   SELECT player_id, team,
          CASE WHEN points   > 9 THEN 1 ELSE 0 END +
          CASE WHEN rebounds > 9 THEN 1 ELSE 0 END +
          CASE WHEN assists  > 9 THEN 1 ELSE 0 END +
          CASE WHEN steals   > 9 THEN 1 ELSE 0 END +
          CASE WHEN blocks   > 9 THEN 1 ELSE 0 END AS doubles
   FROM playergamestats
   ) a
GROUP  BY 1, 2;

SQL Fiddle.

- Erwin Brandstetter

---

TIL：PostgreSQL和MySQL一样，允许通过别名或序号引用GROUP BY中的列。 - Andriy M

Answer 5

使用整数除法和二进制转换

SELECT player_id
     , team
     , SUM(CASE WHEN Doubles = 2 THEN 1 ELSE 0 END) DoubleDouble
     , SUM(CASE WHEN Doubles = 3 THEN 1 ELSE 0 END) TripleDouble
FROM   (SELECT player_id
             , team
             , (BINARY (points DIV 10) > 0)
             + (BINARY (rebounds DIV 10) > 0)
             + (BINARY (assists DIV 10) > 0)
             + (BINARY (steals DIV 10) > 0)
             + (BINARY (blocks DIV 10) > 0)
             AS Doubles
        FROM   PlayerGameStats) d
GROUP BY player_id, team

- Serpiton

Answer 6

这里我想留下一种偶然发现的@Craig Ringers版本的变化，也许对未来的某个人有用。

它使用unnest和array而不是多个UNION ALL。灵感来源：https://stackoverflow.com/questions/1128737/unpivot-and-postgresql

SELECT
  player_id,
  count(CASE WHEN doubles = 2 THEN 1 END) AS doubledoubles,
  count(CASE WHEN doubles = 3 THEN 1 END) AS tripledoubles
FROM (
    SELECT
      player_id, seasonday, count(*) AS doubles
    FROM
    (
        SELECT 
          player_id, 
          seasonday,
          unnest(array['Points', 'Rebounds', 'Assists', 'Steals', 'Blocks']) AS scoretype,
          unnest(array[Points, Rebounds, Assists, Steals, Blocks]) AS score
        FROM PlayerGameStats
    ) stats
    WHERE score >= 10
    GROUP BY player_id, seasonday
) doublestats
GROUP BY player_id;

SQL Fiddle: http://sqlfiddle.com/#!12/4980b/3

- user39509