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-05-08 10:42
0

Ja nie bardzo widzę po czym łączysz te tabele ze sobą? Jak wykluczyć drużyny skoro najlepsza grała z innymi?

Pozostało 580 znaków

2019-05-10 17:43
0

Postaram Ci sie bardziej przybliżyć o co mi chodzi

poniżej
1) tabela mecze
2) tabela zdarzenia zd
3) join do tabel
4) twoje querry z convertem daty

i teraz tak
chciałbym aby querry liczyło co tydzień ( data prze konwertowana na liczbę +7 ) ilość rzutów rożnych dla każdej druzyny oprócz tych które zajmują pierwsze i ostatnie miejsce w danym tygodniu.
tutaj jest trochę mało danych ale przekładając na nie moje założenia to:
po pierwszej kolejce nie policzy nic bo paris sg będzie pierwsza a lorient druga czyli ostatnia
po drugiej kolejce lorient jest już drugie ( paris pierwsze a ajaccio trzecie (ostatnie)) i dla lorient chce miec policzona liczbę rzutów rożnych
itd itd

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
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
select * from MECZE m
join zd z
on m.id=z.id
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
        ,cast (data as varchar(100))+' (' + 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
    order by  Pts desc, GD desc

Pozostało 580 znaków

2019-05-10 19:06
0

No nie do końca:

Pierwszy mecz: Lorient vsParis SG remis 2:2 obie drużyny maja po 1 punkt
Drugi mecz: Ajaccio vs Paris SG 0:0 drużyny mają po 1 punkt

to po drugim meczu punktacja jest taka:

Ajaccio 1
Lorient 1
Paris SG 2

Czyli nie ma nikogo w środku...

Do rozważenia, w rozbiciu na poszczególne tygodnie w roku:

with kolejki as (
    select distinct year(convert(date, data, 104)) y, DATEPART( wk, convert(date, data, 104)) t ,data  from mecze --order by 1
), dane as (
    select 
        DATEPART( wk, convert(date, data, 104)) t
        , year(convert(date, data, 104)) y
        ,MECZE.country
        ,MECZE.Season
        ,home team
        ,case when hg > ag then 3 else 0 end + case when hg = ag then 1 else 0 end Pts,hg,ag
        ,mecze.id
        ,[Corner_kick]  
        ,[Yellow_card]
        ,[Red_card]
    from 
        MECZE
        left join zd on mecze.id=zd.id
    union all
    select 
        DATEPART( wk, convert(date, data, 104))
        , year(convert(date, data, 104)) y
        ,MECZE.country
        ,MECZE.Season
        ,away team
        ,case when  ag > hg then 3 else 0 end + case when hg = ag then 1 else 0 end Pts,ag,hg
        ,mecze.id
        ,[Corner_kick]  
        ,[Yellow_card]
        ,[Red_card]
    from
        MECZE 
        left join zd on mecze.id=zd.id
),  dt as (
select 
    k.y
    ,k.t
    ,country
    ,Season
    ,team
    ,sum(pts) pts
    ,sum([Corner_kick]) ck
    ,sum([Yellow_card]) yc
    ,sum([Red_card]) rc
from 
    kolejki k 
    inner join dane m on (m.t <= k.t and m.y = k.y) or m.y < k.y
group by  
    k.y
    ,k.t
    ,country
    ,Season
    ,team), rpt as (
select 
    *
    ,max(pts) over (partition by y,t) maxp
    , min(pts) over (partition by y,t) minp  
from dt)

select 
    y
    ,t
    ,country
    ,Season
    ,team
    ,pts
    ,case when pts=maxp or pts=minp then null else ck end ck
    ,case when pts=maxp or pts=minp then null else yc end yc
    ,case when pts=maxp or pts=minp then null else rc end rc
 from rpt
 order by y,t,pts desc

https://dbfiddle.uk/?rdbms=sq[...]dda40a436e7c001540fff3f5dbcf0

Pozostało 580 znaków

2019-05-13 22:45
0

Oczywiście z punktacja masz racje ale drugim parametrem segregującym jest liczba zdobytych bramek i dlatego Lorient jest na 2 miejscu

A gdybym chciał wykluczyć 6 drożynę w tabeli to przez rank >? czy jest prostszy sposób ?

Jestem teraz na urlopie i nie mam czasu sprawdzić tego querry ale dziękuje za pomoc.

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