2 sütunda sayımı sorgulama

İki takımın ne sıklıkla oynadığını gösteren bir sql sorgusu yazmaya çalışıyorum.

Id | Team1 | Team2 | Date 
1  |   A   |   B   | 25/5/11
2  |   B   |   A   | 26/5/11
3  |   A   |   C   | 27/5/11
4  |   C   |   B   | 28/5/11
5  |   A   |   B   | 28/5/11

sonuç şöyle olmalı:

A vs B => 3
A vs C => 1
C vs B => 1

A-B ve B-A'yı farklı olarak saymak kolay bir sorundur. Ama birlikte sayılmalarını sağlayamıyorum.

Herhangi bir öneri?

 6
katma düzenlendi
Görünümler: 7
kaynak
ro ru fr es pt de hi bn ar kk uz uk
Hangi SQL sürümünü kullanıyorsunuz?
katma yazar leoinfo, kaynak
Takım isimleri her şey olabilir. Daha hızlı yazmak için A ve B'yi kullandım.
katma yazar Sorskoot, kaynak
Bu durumda MySQL'i canlı veritabanım için kullanıyorum ...
katma yazar Sorskoot, kaynak
Her takımı temsil eden bir numara var mı? yani ekip tablosundan?
katma yazar galchen, kaynak

4 cevap

Gruplandırmadan önce ekipleri yeniden düzenlemek için bir alt sorgu kullandım.

SELECT first_team, second_team, count(*)
FROM (
    SELECT 
        CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END AS first_team,
        CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END AS second_team
    FROM table
) a
GROUP BY first_team, second_team;
6
katma
kaynak
Bu işe yarayacak, ancak indeksleri hiç kullanmıyor, bu yüzden çok fazla ekibiniz varsa cevabımı kullanın.
katma yazar Ariel, kaynak 
SELECT Team1, Team2, SUM(num) FROM (
   SELECT Team1, Team2, COUNT(*) num
   FROM table_name
   GROUP BY Team1, Team2
  UNION ALL
   SELECT Team2, Team1, COUNT(*) num
   FROM table_name
   GROUP BY Team2, Team1
) combined
WHERE Team1 < Team2
GROUP BY Team1, Team2

Düzenleme: Gerektiğinde takımları tersine çevirmek için güncellendi.

Not: Bu, diğer cevaplarda verdiğiniz CASE'i kullanan sürümlerden çok daha hızlı çalışacaktır çünkü dizinleri tam olarak kullanacaktır.

Düzen2: Nerede indekslerle daha hızlı olacağı taşındı.

5
katma
kaynak
@Ariel - Sanırım sunucuya gereksiz bazı yükler yüklediniz. Gönderime bir bak ve "Stil 4" etiketli kısmı kontrol et. Uygulama planına göre, bu oran% 60 daha hızlı olmalı.
katma yazar leoinfo, kaynak
@Shark Ah, bunu özledim. Hiç sorun değil, cevabımı güncelledi.
katma yazar Ariel, kaynak
@Sorskoot İki indeks koyun: Team1, Team2 ve Team2, Team1'de ikinci bir indeks
katma yazar Ariel, kaynak
Dizinler ile daha hızlı olması için düzenledi. burada aslında isteğe bağlıdır - dışarıda bırakırsanız her gruplandırmayı iki kez alırsınız - bu yararlı olabilir çünkü yalnızca Team1 sütununu aramanız gerekir. o takımın her iki sütununu da kontrol etmek yerine belirli bir takım.
katma yazar Ariel, kaynak
@leoinfo bu sadece daha hızlı çünkü çok az veriye sahipsiniz. Veritabanını her bir sırayı gerçekten sıralaması, gruplaması ve sayması için zorluyorsunuz. Madende sadece indekse girmeli ve doğrudan endekste aynı yaprak sayısını elde etmeli, aslında onları saymaya gerek kalmamalı, onları ayırmaya ya da gruplandırmaya gerek kalmayacak; İçerik.
katma yazar Ariel, kaynak
Bu tam olarak benim açımdan ... :)
katma yazar Sorskoot, kaynak
Tablo oldukça büyüyebilir ... Bu yüzden indeks kullanımı muhtemelen gerekli ... Teşekkürler ..
katma yazar Sorskoot, kaynak
Bu doğru sonucu vermez. Bu sorguda A -> B ve B -> A arasında bir ayrım var. OP, A ile B'yi B ile A ile birleştirecek arıyor.
katma yazar user596075, kaynak
@ Ariel iyi çalışıyor! +1
katma yazar user596075, kaynak

Hedefinize ulaşmanın birkaç yolu vardır:

SELECT Teams, Games = COUNT(*) FROM 
(
  SELECT 
    Teams = CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END + ' vs ' +
            CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END
  FROM MY_TABLE 
) AS T
GROUP BY Teams

VEYA, SQL 2005/2008 kullanıyorsanız

;WITH T AS (
  SELECT 
    Teams = CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END + ' vs ' +
            CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END
  FROM MY_TABLE 
)
SELECT Teams, Games = COUNT(*) FROM T GROUP BY Teams

Yukarıdakilerin her ikisi de aynı sonucu verecektir.

/*
Teams     Games
-------|------
A vs B  3
A vs C  1
B vs C  1
*/

İşte oynayabileceğiniz bir komut dosyası:

/* TEST DATA */
DECLARE @t AS TABLE ( ID INT, Team1 CHAR(1), Team2 CHAR(1), playdate [DATETIME] )
INSERT INTO @t (Team1 , Team2 , playdate)
          SELECT 'A' , 'B', '20110525'
UNION ALL SELECT 'B' , 'A', '20110526'    
UNION ALL SELECT 'A' , 'C', '20110527'    
UNION ALL SELECT 'C' , 'B', '20110528'    
UNION ALL SELECT 'A' , 'B', '20110528'    


/* STYLE 1 */    
;WITH T AS (
  SELECT 
    Teams = CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END + ' vs ' +
            CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END
  FROM @t 
)
SELECT Teams, Games = COUNT(*) FROM T GROUP BY Teams

/* STYLE 2 */
SELECT Teams, Games = COUNT(*) FROM 
(
  SELECT 
    Teams = CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END + ' vs ' +
            CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END
  FROM @t 
) AS T
GROUP BY Teams

/ * VEYA, sadece sütunları değiştirmek için bunu kullanın * /

/* STYLE 3 */    
;WITH T AS (
  SELECT 
      Team1 = CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END 
    , Team2 = CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END
  FROM @t 
)
SELECT Team1 , Team2, Games = COUNT(*) FROM T GROUP BY Team1 , Team2

/* STYLE 4 */
SELECT Team1 , Team2, Games = COUNT(*) FROM 
(
  SELECT 
      Team1 = CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END 
    , Team2 = CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END
  FROM @t 
) AS T
GROUP BY Team1 , Team2
1
katma
kaynak 
SELECT 
  (CASE WHEN Team1Team2 THEN Team1 ELSE Team2) Team2,
  COUNT(*)  cnt
FROM <table> 
GROUP BY 
  (CASE WHEN Team1Team2 THEN Team1 ELSE Team2) Team2
1
katma
kaynak