Policz, gdzie dwie lub więcej kolumn z rzędu przekracza określoną wartość [koszykówka, podwójne podwójne, trzyosobowe podwójne]

Gram w grę w koszykówkę, która pozwala na generowanie statystyk jako pliku bazy danych, dzięki czemu można obliczyć statystyki, które nie są zaimplementowane w grze. Do tej pory nie miałem problemu z obliczeniem statystyk, które chciałem, ale teraz napotkałem problem: liczenie liczby podwójnych i / lub potrójnych podwójnych wyników gracza w sezonie na podstawie jego statystyk gry.

Definicja podwójnego i potrójnego podwójnego jest następująca:

Podwójny podwójny:

Podwójne podwójne jest zdefiniowane jako występ, w którym gracz gromadzi dwucyfrową liczbę w dwóch z pięciu kategorii statystycznych - punktów, zbiórek, asyst, kradzieży i zablokowanych strzałów - w grze.

Potrójne podwójne:

Potrójny podwójny jest definiowany jako występ, w którym gracz gromadzi dwucyfrową liczbę w trzech z pięciu kategorii statystycznych - punktów, zbiórek, asyst, kradzieży i zablokowanych strzałów - w grze.

Quadruple-double (dodano dla wyjaśnienia)

Poczwórny podwójny jest definiowany jako występ, w którym gracz gromadzi dwucyfrową liczbę w czterech z pięciu kategorii statystycznych - punktów, zbiórek, asyst, kradzieży i zablokowanych strzałów - w grze.

Tabela „PlayerGameStats” przechowuje statystyki dla każdej gry, w którą gra gracz, i wygląda następująco:

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

Wynik, który chcę osiągnąć, wygląda następująco:

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

Jedyne rozwiązanie, jakie do tej pory znalazłem, jest tak okropne, że aż wymiotuję ...; o) ... Wygląda to tak:

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

... a teraz prawdopodobnie też rzygasz (lub mocno się śmiejesz) po przeczytaniu tego. Nie napisałem nawet wszystkiego, co byłoby potrzebne do uzyskania wszystkich podwójnych kombinacji podwójnych, i pominąłem instrukcję case dla potrójnych podwójnych, ponieważ jest to jeszcze bardziej śmieszne.

Czy jest na to lepszy sposób? Albo ze strukturą tabeli, którą mam, albo z nową strukturą tabeli (mógłbym napisać skrypt do konwersji tabeli).

Mogę używać MySQL 5.5 lub PostgreSQL 9.2.

Oto link do SqlFiddle z przykładowymi danymi i moim okropnym rozwiązaniem, które zamieściłem powyżej: http://sqlfiddle.com/#!2/af6101/3

Zauważ, że tak naprawdę nie jestem zainteresowany quadruple-double (patrz wyżej), ponieważ nie występują one w grze, w którą gram, o ile wiem, ale byłby plus, gdyby zapytanie można było łatwo rozbudować bez konieczności przepisywania konta dla poczwórnych podwójnych.

Nie wiem, czy to najlepszy sposób. Najpierw wybrałem opcję, aby dowiedzieć się, czy statystyka jest dwucyfrowa i przypisać jej 1, jeśli tak jest. Zsumuj wszystkie, aby dowiedzieć się, jaka jest łączna liczba podwójnych cyfr na grę. Stamtąd wystarczy podsumować wszystkie dwójki i trójki. Wydaje się działać

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

Wypróbuj to (pracował dla mnie na 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

Lub nawet krócej, odrywając kod JChao z jego odpowiedzi, ale usuwając niepotrzebne CASEinstrukcje, ponieważ wyrażenie boolowskie ocenia na {1,0}, gdy {True, False}:

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

Na podstawie komentarzy, że powyższy kod nie będzie działał w PostgreSQL, ponieważ nie lubi robić boolean + boolean. Nadal nie lubię CASE. Oto wyjście z PostgreSQL (9.3), przesyłając do 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

Oto kolejne spojrzenie na problem.

Tak myślę, że zasadniczo pracujesz z danymi przestawnymi dla bieżącego problemu, więc pierwszą rzeczą do zrobienia jest odwrócenie go. Niestety PostgreSQL nie zapewnia dobrych narzędzi do tego, więc bez wchodzenia w dynamiczne generowanie SQL w PL / PgSQL, możemy przynajmniej:

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

To sprawia, że ​​dane są bardziej plastyczne, choć z pewnością nie są ładne. Zakładam tutaj, że (player_id, seasonday) jest wystarczający, aby jednoznacznie zidentyfikować graczy, tj. Identyfikator gracza jest unikalny dla różnych drużyn. Jeśli tak nie jest, musisz podać wystarczającą ilość innych informacji, aby podać unikalny klucz.

Dzięki niepodzielnym danym można teraz filtrować i agregować je w użyteczny sposób, na przykład:

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;

Jest to dalekie od ładnego i prawdopodobnie nie jest tak szybkie. Można go jednak utrzymać, wymagając minimalnych zmian w celu obsługi nowych typów statystyk, nowych kolumn itp.

To bardziej „hej, myślałeś o” niż poważna sugestia. Celem było modelowanie kodu SQL tak, aby odpowiadał opisowi problemu tak bezpośrednio, jak to możliwe, a nie przyspieszenie.

Było to znacznie łatwiejsze dzięki zastosowaniu rozsądnych wielowartościowych wstawek i cytowaniu ANSI w SQL zorientowanym na MySQL. Dziękuję Ci; miło jest nie oglądać się za raz. Wszystko, co musiałem zmienić, to syntetyczne generowanie kluczy.

To, co @ Joshua wyświetla dla MySQL , działa również w Postgres. Booleanwartości można rzutować integeri sumować. Obsada musi być jednak wyraźna. Sprawia, że ​​kod jest bardzo krótki:

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;

• Wykorzystując również krótszą metodę liczenia podwójnych w zewnętrznej SELECT.
  Szczegóły w tej powiązanej odpowiedzi.

Jednak CASE- chociaż bardziej szczegółowy - jest zwykle nieco szybszy. I bardziej przenośny, jeśli to powinno mieć znaczenie:

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.

Korzystanie z dzielenia liczb całkowitych i rzutowania binarnego

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

Po prostu chcę zostawić tutaj odmianę wersji @Craig Ringers, którą znalazłem przypadkiem, może przyda się komuś w przyszłości.

Zamiast wielu UNION ALL używa unnest i tablic. Źródło inspiracji: /programming/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