Tabela pilkarska SQL pivot daty

Odpowiedz Nowy wątek
2019-03-14 23:05
0

Witam Panowie mam problem
Chce stworzyć tabele przedstawiająca mecze piłki nożnej która uwzględniała by ostatnie 5 meczy każdej z drużyn i nie mogę sobie dać rady.
Próbowałem przez poniższe querry z row number <5 i niestety nie działa . Byłbym wdzięczny za jakieś podpowiedzi
chciałem zrobić podzapytanie i pivotem odwrócić ale tez nie za bardzo wiem jak się do tego zabrać ??

zapytanie

with table_a as (
    select country,Season, data, home team, hg, ag from MECZE
      union all
    select country,Season, data, away team, ag, hg from MECZE 
    ) 
,table_b as (
select * from (
    select a.*
    ,row_number() over (partition by a.country ,a.team order by a.data desc) as row_num
    from table_a a) X
where row_num <= 333 )

select 
    team, 
    count(*) MP, 
    count(case when hg > ag then 1 end) W, 
    count(case when hg = ag then 1 end) D, 
    count(case when hg < ag then 1 end) L,
    sum(hg) GF,
    sum(ag) GA,
    sum(hg) - sum(ag) GD,
    sum(case when hg > ag then 3 else 0 end + case when hg = ag then 1 else 0 end) Pts
    from table_b
    where country='france' and  Season ='2012/2013'
group by country, team 
order by country, Pts desc

wynik

team    MP  W   D   L   GF  GA  GD  Pts
Paris SG    38  25  8   5   69  23  46  83
Marseille   38  21  8   9   42  36  6   71
Lyon    38  19  10  9   61  38  23  67
Nice    38  18  10  10  57  46  11  64
St Etienne  38  16  15  7   60  32  28  63
Lille   38  16  14  8   59  40  19  62
Bordeaux    38  13  16  9   40  34  6   55

tabela mecze

ID  Country League  Season  data    Time    Home    Away    HG  AG  Res
43086   France  Ligue 1 2012/2013   10.08.2012  21:00:00.0000000    Montpellier Toulouse    1   1   D
43087   France  Ligue 1 2012/2013   11.08.2012  21:00:00.0000000    Evian Thonon Gaillard   Bordeaux    2   3   A
43088   France  Ligue 1 2012/2013   11.08.2012  21:00:00.0000000    Nancy   Brest   1   0   H
43089   France  Ligue 1 2012/2013   11.08.2012  21:00:00.0000000    Nice    Ajaccio 0   1   A
43090   France  Ligue 1 2012/2013   11.08.2012  21:00:00.0000000    Paris SG    Lorient 2   2   D
43091   France  Ligue 1 2012/2013   11.08.2012  21:00:00.0000000    Rennes  Lyon    0   1   A
43092   France  Ligue 1 2012/2013   11.08.2012  21:00:00.0000000    Sochaux Bastia  2   3   A
43093   France  Ligue 1 2012/2013   11.08.2012  21:00:00.0000000    St Etienne  Lille   1   2   A
43094   France  Ligue 1 2012/2013   11.08.2012  21:00:00.0000000    Troyes  Valenciennes    0   1   A
43095   France  Ligue 1 2012/2013   12.08.2012  21:00:00.0000000    Reims   Marseille   0   1   A
43096   France  Ligue 1 2012/2013   17.08.2012  21:00:00.0000000    Lille   Nancy   1   1   D
43097   France  Ligue 1 2012/2013   18.08.2012  21:00:00.0000000    Bastia  Reims   2   1   H
43098   France  Ligue 1 2012/2013   18.08.2012  21:00:00.0000000    Brest   Evian Thonon Gaillard   1   0   H
43099   France  Ligue 1 2012/2013   18.08.2012  21:00:00.0000000    Lorient Montpellier 2   1   H
43100   France  Ligue 1 2012/2013   18.08.2012  21:00:00.0000000    Lyon    Troyes  4   1   H
43101   France  Ligue 1 2012/2013   18.08.2012  21:00:00.0000000    Toulouse    St Etienne  2   1   H
43102   France  Ligue 1 2012/2013   18.08.2012  21:00:00.0000000    Valenciennes    Nice    0   0   D
43103   France  Ligue 1 2012/2013   19.08.2012  21:00:00.0000000    Ajaccio Paris SG    0   0   D
43104   France  Ligue 1 2012/2013   19.08.2012  21:00:00.0000000    Bordeaux    Rennes  1   0   H
43105   France  Ligue 1 2012/2013   19.08.2012  21:00:00.0000000    Marseille   Sochaux 2   0   H
43106   France  Ligue 1 2012/2013   24.08.2012  21:00:00.0000000    Evian Thonon Gaillard   Lyon    1   1   D
43107   France  Ligue 1 2012/2013   25.08.2012  21:00:00.0000000    Nancy   Toulouse    0   1   A
43108   France  Ligue 1 2012/2013   25.08.2012  21:00:00.0000000    Nice    Lille   2   2   D

Pozostało 580 znaków

2019-03-17 22:39
0
row_num <= 333

Czemu chcąc ostanie 5 meczy pytasz o ostatnie 333?

to byl moj test. 5 dziala dla calej tabeli a nie dla kazdej druzyny. Dopiero jak wpisze liczbe wieksza od ilosci spotkan w calej lidze to pojawia sie prawidlowa tabela. - Kenshin206 2019-03-18 08:13

Pozostało 580 znaków

2019-03-25 22:06
0

Ktos ma jakis pomysl ?

Pozostało 580 znaków

2019-03-26 08:37
0

Trudno mieć pomysł jak to co chcesz (ostatnie 5 meczów) ma się nijak do danych które przedstawiłeś, proponuje przejść do szczegółów, czyli pokaż dane dla jednej drużyny np. Paris SG pokaż wynik dla zapytania
i opisz co jest nie tak

Pozostało 580 znaków

2019-03-26 18:53
0

przykladowe dane dla Paris SG rok 2012/2013

ID;Country;League;Season;data;Time;Home;Away;HG;AG;Res
41839;France;Ligue 1;2012/2013;11.08.2012;21:00:00.0000000;Paris SG;Lorient;2;2;D
41852;France;Ligue 1;2012/2013;19.08.2012;21:00:00.0000000;Ajaccio;Paris SG;0;0;D
41863;France;Ligue 1;2012/2013;26.08.2012;21:00:00.0000000;Paris SG;Bordeaux;0;0;D
41873;France;Ligue 1;2012/2013;02.09.2012;21:00:00.0000000;Lille;Paris SG;1;2;A
41875;France;Ligue 1;2012/2013;14.09.2012;21:00:00.0000000;Paris SG;Toulouse;2;0;H
41886;France;Ligue 1;2012/2013;22.09.2012;21:00:00.0000000;Bastia;Paris SG;0;4;A
41900;France;Ligue 1;2012/2013;29.09.2012;21:00:00.0000000;Paris SG;Sochaux;2;0;H
41914;France;Ligue 1;2012/2013;07.10.2012;21:00:00.0000000;Marseille;Paris SG;2;2;D
41919;France;Ligue 1;2012/2013;20.10.2012;21:00:00.0000000;Paris SG;Reims;1;0;H
41928;France;Ligue 1;2012/2013;27.10.2012;21:00:00.0000000;Nancy;Paris SG;0;1;A
41936;France;Ligue 1;2012/2013;03.11.2012;21:00:00.0000000;Paris SG;St Etienne;1;2;A
41952;France;Ligue 1;2012/2013;11.11.2012;21:00:00.0000000;Montpellier;Paris SG;1;1;D
41958;France;Ligue 1;2012/2013;17.11.2012;21:00:00.0000000;Paris SG;Rennes;1;2;A
41967;France;Ligue 1;2012/2013;24.11.2012;21:00:00.0000000;Paris SG;Troyes;4;0;H
41980;France;Ligue 1;2012/2013;01.12.2012;21:00:00.0000000;Nice;Paris SG;2;1;H
41988;France;Ligue 1;2012/2013;08.12.2012;21:00:00.0000000;Paris SG;Evian Thonon Gaillard;4;0;H
41997;France;Ligue 1;2012/2013;11.12.2012;21:00:00.0000000;Valenciennes;Paris SG;0;4;A
42013;France;Ligue 1;2012/2013;16.12.2012;21:00:00.0000000;Paris SG;Lyon;1;0;H
42015;France;Ligue 1;2012/2013;21.12.2012;21:00:00.0000000;Brest;Paris SG;0;3;A
42025;France;Ligue 1;2012/2013;11.01.2013;21:00:00.0000000;Paris SG;Ajaccio;0;0;D
42043;France;Ligue 1;2012/2013;20.01.2013;21:00:00.0000000;Bordeaux;Paris SG;0;1;A
42053;France;Ligue 1;2012/2013;27.01.2013;21:00:00.0000000;Paris SG;Lille;1;0;H
42055;France;Ligue 1;2012/2013;01.02.2013;21:00:00.0000000;Toulouse;Paris SG;0;4;A
42065;France;Ligue 1;2012/2013;08.02.2013;21:00:00.0000000;Paris SG;Bastia;3;1;H
42084;France;Ligue 1;2012/2013;17.02.2013;21:00:00.0000000;Sochaux;Paris SG;3;2;H
42094;France;Ligue 1;2012/2013;24.02.2013;21:00:00.0000000;Paris SG;Marseille;2;0;H
42098;France;Ligue 1;2012/2013;02.03.2013;21:00:00.0000000;Reims;Paris SG;1;0;H
42109;France;Ligue 1;2012/2013;09.03.2013;21:00:00.0000000;Paris SG;Nancy;2;1;H
42123;France;Ligue 1;2012/2013;17.03.2013;21:00:00.0000000;St Etienne;Paris SG;2;2;D
42125;France;Ligue 1;2012/2013;29.03.2013;21:00:00.0000000;Paris SG;Montpellier;1;0;H
42139;France;Ligue 1;2012/2013;06.04.2013;21:00:00.0000000;Rennes;Paris SG;0;2;A
42151;France;Ligue 1;2012/2013;13.04.2013;21:00:00.0000000;Troyes;Paris SG;0;1;A
42159;France;Ligue 1;2012/2013;21.04.2013;21:00:00.0000000;Paris SG;Nice;3;0;H
42172;France;Ligue 1;2012/2013;28.04.2013;21:00:00.0000000;Evian Thonon Gaillard;Paris SG;0;1;A
42183;France;Ligue 1;2012/2013;05.05.2013;21:00:00.0000000;Paris SG;Valenciennes;1;1;D
42194;France;Ligue 1;2012/2013;12.05.2013;21:00:00.0000000;Lyon;Paris SG;0;1;A
42198;France;Ligue 1;2012/2013;18.05.2013;21:00:00.0000000;Paris SG;Brest;3;1;H
42210;France;Ligue 1;2012/2013;26.05.2013;21:00:00.0000000;Lorient;Paris SG;1;3;A

W załączniku screen jak chciałbym zęby to wyglądało ...

edytowany 1x, ostatnio: Kenshin206, 2019-03-26 20:54
daj te dane rozdzielone przecinkiem, albo średnikiem bo nie ma jak tego zaczytać, a bawic mi się nie chce z robieniem tego csv - Panczo 2019-03-26 19:40

Pozostało 580 znaków

2019-03-26 23:06

To musisz zrobić pivta, wymaga dopracowania:

with dane as (
    select 
        country
        ,Season
        ,data
        ,home team
        ,hg
        ,ag 
    from 
        MECZE
    union all
    select 
        country
        ,Season
        ,data
        ,away team
        ,ag
        ,hg 
    from
        MECZE 
) 
,last5 as (
select 
    * 
from (
    select 
        *
        ,row_number() over (partition by country ,team order by data desc) as mecz
    from 
        dane) X
    where 
        mecz <= 5 
)
,punkty as (
select 
    team, 
    count(*) MP, 
    count(case when hg > ag then 1 end) W, 
    count(case when hg = ag then 1 end) D, 
    count(case when hg < ag then 1 end) L,
    sum(hg) GF,
    sum(ag) GA,
    sum(hg) - sum(ag) GD,
    sum(case when hg > ag then 3 else 0 end + case when hg = ag then 1 else 0 end) Pts
from 
    dane
where 
    country='france' 
    and  Season ='2012/2013'
group by 
    country
    ,team 
)
,pvtLast5 as (
SELECT 
    team 
    ,[1]
    ,[2]
    ,[3]
    ,[4]
    ,[5]
FROM  
    (SELECT 
        team
        ,data+' (' + case when hg > ag then '3' when hg = ag then '1' else '0' end+')' data
        ,mecz 
    FROM 
        last5) AS src  
    PIVOT  (  
        max(data) FOR mecz IN ( [1], [2], [3], [4],[5])  
    ) AS pvt
)

select 
    p.*
    ,[1]
    ,[2]
    ,[3]
    ,[4]
    ,[5]
from 
    punkty p
    inner join pvtlast5 l5 on p.team=l5.team

u ciebie data+' (' + case when hg > ag then '3' when hg = ag then '1' else '0' end+')' data data będzie wymagała converta/casta na varchar (zakładam że to pole jest datą), u mnie w przykładzie jest varchar bo nie chciało mi się robić convertów do silnika fiddle.
Dodatkow pivot wymaga dodatkowych kolumn bo mecze liczy globalnie, a powinien chyba w ramch sezonu itd.

Wynik zapytania: https://dbfiddle.uk/?rdbms=sq[...]332e1b68a51ec01e34398578c0f10

Genialne !!! nie wiem jeszcze jak ale działa ;-) Wielkie thx - Kenshin206 2019-03-27 20:56

Pozostało 580 znaków

2019-05-01 11:43
0

Panowie a teraz mam kolejny problem mam tabele piłkarze/ zdarzenia meczowe itd i juz sobie połączyłem tak ze to wszystko ladnie hula
Jednak chciałbym zrobić teraz pętle która by liczyła cos po wskazanych prze zemnie parametrach
1) gdzie powinienem ta pętle wrzucić przed zapytaniem w środku ?
2) chciałbym zeby to liczyła co kolejkę. wiec wymyśliłem ze daty przerobie na cyfry znajdę min tak zeby to był powiedzmy i będę dodawał 7. Czy to jest dobry sposób czy jest jakiś prostszy ;-)?

Pozostało 580 znaków

2019-05-01 11:49
0

Samo stwierdzenie pętla, to coś co nie bardzo pasuje do SQL...
Pomijając już czy to ma sens, to jak mamy coś doradzić skoro nie wiemy co chcesz liczyć...

Pozostało 580 znaków

2019-05-06 22:23
0

Chodzi mi o to ze mam tabele np zdarzenia meczowe / zawodnicy itd
i chciałbym abym mógł po wybraniu przedziału czasowego (data konwertowana na liczbę) policzyć np ilość rzutów rożnych bez wybranych miejsc w rankingu

Przykładowo chciałbym policzyć ilość rzutów rożnych w wersji tabelarycznej takiej jak powyżej dla przedziału dat od xx do xx ale zeby nie brać pod uwagę pierwszej i ostatniej drużyny ( pod względem punktów)

Pozostało 580 znaków

2019-05-07 11:03
0

Proponuje przygotować jakis zestaw danych, żebysmy mieli o czym rozmawiać, inaczej to nie zadziała...

Pozostało 580 znaków

2019-05-07 22:47
0

Tabela zdarzenia

id_z    ID  Country League  Season  Corner kick Red card    Yellow card
5567;41839;France;Ligue 1;2012/2013;11;0;3
5568;41852;France;Ligue 1;2012/2013;12;0;3
5569;41863;France;Ligue 1;2012/2013;14;0;2
5570;41873;France;Ligue 1;2012/2013;7;1;4
5571;41875;France;Ligue 1;2012/2013;7;0;5
5572;41886;France;Ligue 1;2012/2013;9;0;4
5573;41900;France;Ligue 1;2012/2013;8;0;3
5574;41914;France;Ligue 1;2012/2013;10;0;5
5575;41919;France;Ligue 1;2012/2013;11;0;2
5576;41928;France;Ligue 1;2012/2013;10;0;1
5577;41936;France;Ligue 1;2012/2013;4;0;1
5578;41952;France;Ligue 1;2012/2013;13;0;0
5579;41958;France;Ligue 1;2012/2013;5;0;2
5580;41967;France;Ligue 1;2012/2013;6;0;3
5581;41980;France;Ligue 1;2012/2013;9;0;2
5582;41988;France;Ligue 1;2012/2013;8;0;3
5583;41997;France;Ligue 1;2012/2013;6;0;3
5584;42013;France;Ligue 1;2012/2013;9;1;2
5585;42015;France;Ligue 1;2012/2013;9;0;4
5586;42025;France;Ligue 1;2012/2013;8;0;3
5587;42043;France;Ligue 1;2012/2013;10;0;5
5588;42053;France;Ligue 1;2012/2013;11;0;2
5589;42055;France;Ligue 1;2012/2013;10;0;1
5590;42065;France;Ligue 1;2012/2013;9;0;4
5591;42084;France;Ligue 1;2012/2013;8;0;3
5592;42094;France;Ligue 1;2012/2013;10;0;5
5593;42098;France;Ligue 1;2012/2013;11;0;2
5594;42109;France;Ligue 1;2012/2013;10;0;1
5595;42123;France;Ligue 1;2012/2013;7;0;2
5596;42125;France;Ligue 1;2012/2013;8;0;2
5597;42139;France;Ligue 1;2012/2013;12;0;3
5598;42151;France;Ligue 1;2012/2013;8;0;2
5599;42159;France;Ligue 1;2012/2013;9;0;4
5600;42172;France;Ligue 1;2012/2013;8;0;3
5601;42183;France;Ligue 1;2012/2013;10;0;5
5602;42194;France;Ligue 1;2012/2013;11;0;2
5603;42198;France;Ligue 1;2012/2013;10;0;1
5604;42210;France;Ligue 1;2012/2013;12;0;3

zakres dat od 40937 (2012-01-29)+7 do 41580 (2013-11-02)

i chciałbym wykluczyć pierwsza i ostatnia drożynę z tabeli z querry powyzej

edytowany 2x, ostatnio: Kenshin206, 2019-05-07 22:55

Pozostało 580 znaków

Odpowiedz
Liczba odpowiedzi na stronę

1 użytkowników online, w tym zalogowanych: 0, gości: 1, botów: 0