如何根据名称的Streak列获取hattricks的数量。 [英] how to get the count of hattricks based on the Streak column for the name.

问题描述

MatchNo	Match_date      Name    Streak
1       2015-04-08      Aditya	1
2       2015-04-09      Aditya	0
3       2015-04-10      Aditya	0
4       2015-04-11      Aditya	0
5       2015-04-11      Aditya	0
6       2015-04-12      Aditya	0
7       2015-04-12      Aditya	1
8       2015-04-13      Aditya	1
9       2015-04-14      Aditya	0
10      2015-04-15      Aditya	1
11      2015-04-16      Aditya	0
12      2015-04-17      Aditya	0
13      2015-04-18      Aditya	0
14      2015-04-18      Aditya	0
15      2015-04-19      Aditya	1
16      2015-04-19      Aditya	1
17      2015-04-20      Aditya	1
18      2015-04-21      Aditya	1
19      2015-04-22      Aditya	1
20      2015-04-22      Aditya	0
21      2015-04-23      Aditya	0

[edit]已添加代码块 - OriginalGriff [/ edit]

[edit]Code block added - OriginalGriff[/edit]

推荐答案

这是相当常见的前任在数据中发现条纹（运行，模式）的问题。



有一个很好的文章 [ ^ ]由Jeff Smith在SqlTeam.com指出你需要利用的技巧 - 胜利的每次运行前面都有一个（单个） ） 失利。您可以通过将条纹值与上一行的值进行比较来识别新的连胜。在以下sql

This is the fairly common exercise of finding streaks (runs, patterns) in your data.

There is an excellent article[^] by Jeff Smith at SqlTeam.com that points out the "trick" you need exploit - each "run" of wins is preceded by a (single) loss. You can use this to identify a new winning streak by comparing its streak value against the value from the previous row. Give each block of wins and losses a number - RunGroup in the following sql

SELECT Match_Date, MatchNo, Player,
  (SELECT COUNT(*)
   FROM results G
   WHERE G.streak <> GR.streak
   AND G.MatchNo <= GR.MatchNo) as RunGroup
FROM results GR
WHERE streak = 1

哪个会得到以下结果

Which will give the following result

Match_date     MatchNo  Player       RunGroup
2015-04-08	1	Aditya		0
2015-04-12	7	Aditya		5
2015-04-13	8	Aditya		5
2015-04-15	10	Aditya		6
2015-04-19	15	Aditya		10
2015-04-19	16	Aditya		10
2015-04-20	17	Aditya		10
2015-04-21	18	Aditya		10
2015-04-22	19	Aditya		10

如果我们将该sql转换为公用表表达式，我们可以使用GROUP BY来计算每个RunGroup中匹配数的计数：

If we turn that sql into a Common Table Expression we can then use GROUP BY to get a count of the number of matches in each RunGroup:

;WITH CTE AS (
SELECT Match_Date, MatchNo, Player,
  (SELECT COUNT(*) 
   FROM results G 
   WHERE G.streak <> GR.streak 
   AND G.MatchNo <= GR.MatchNo) as RunGroup 
FROM results GR
WHERE streak = 1 
)
SELECT Player, 
  COUNT(*)
FROM CTE
GROUP BY Player, RunGroup

给出结果

Aditya  1
Aditya  2
Aditya  1
Aditya  5

因为你只对hattricks感兴趣（连续3胜的序列）然后你需要使用

Because you are only interested in hattricks (sequences of 3 wins in a row) then you need to restrict the results using

HAVING COUNT(*) >= 3

因此您只能获得

Aditya  5

这一行要获得预期结果，您还需要更改最终 COUNT（*）以获取（整数）值除以3，即

To get your expected result you also need to change the final COUNT(*) to get the (integer) value divided by 3 i.e.

COUNT(*) / 3 as Games

这篇关于如何根据名称的Streak列获取hattricks的数量。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！