'(DB) MySql/Select sql' 카테고리의 글 목록

mysql rank 구하기 (팀이 존재 하는 동일등수)

(DB) MySql/Select sql 2017. 1. 16. 17:21

** mysql rank 구하기 (팀이 존재 하는 동일등수) **

1. 정의

1. 반별로 석차 구하기
2. 팀별 동일점수이며 같은 석차가 부여되어야 함
3. 팀별로 동일석차가 존재 할 수 있음.

2. 자료 예제

-------------------------------------------
반 : 이름 : 팀명 : 점수 : 석차
-------------------------------------------
1반 강감찬 A 100 1
1반 차명이 A 100 1

1반   이순신 B 96 2
1반 골동품 B 96 2

1반 홍길동 C 96 2
1반 결사대 C 96 2

1반   김유신 D 90 4
1반   김유신 D 90 4

-------------------------------------------
2반 말똥고리 A    80 1
2반 구리구리 A    80 1
2반 마리마리 A    80 1

2반 장수말벌 B    70 2
2반 벌똥말똥 B    70 2
2반 쉬벌버벌 B    70 2

2반 꿀벌 C 60 3
2반 말벌 C 60 3
2반 쇠벌 C 60 3

2반 나비 D    60 3
2반 너비 D    60 3
2반 고비 D    60 3
-------------------------------------------------------

3. SQL 예제

SELECT a.game_part_cd, a.enter_team, a.orgn_cd, a.player_seq, a.part_rope_cnt
	 , @rn  := CASE WHEN @part != a.game_part_cd 
						 THEN 1
						 ELSE (CASE WHEN @cnt = a.part_rope_cnt THEN @rn + 0 ELSE @rn + 1 + @same END)
				END rank
	 , @same := CASE WHEN @cnt = a.part_rope_cnt AND (@orgn != a.orgn_cd OR @part != a.game_part_cd OR @team != a.enter_team)
					 THEN @same + 1 
					 ELSE (CASE WHEN @same > 0 AND @cnt = a.part_rope_cnt THEN @same ELSE 0 END)
				END AS same
	 , @orgn := a.orgn_cd
	 , @part := a.game_part_cd
	 , @team := a.enter_team
	 , @cnt  := a.part_rope_cnt
	 
  FROM (SELECT pl.orgn_cd, a.player_seq, a.game_part_cd, a.part_rope_cnt , a.enter_team
		  FROM hanjul.TPlayerInGame a INNER JOIN hanjul.TPlayer pl
			   ON (    a.contest_yy = pl.contest_yy
				   AND a.contest_seq = pl.contest_seq
				   AND a.player_seq  = pl.player_seq )
		 WHERE a.contest_yy    = '2016'
		   AND a.contest_seq   = 11
		   AND a.game_cd       = '0080' -- 0080  0010
		   AND a.part_rope_cnt > 0
		ORDER BY a.game_part_cd ASC, a.part_rope_cnt DESC, a.player_seq) a
	 , (SELECT @part := '', @cnt := 0, @rn := 0, @same := 0) b

저작자표시

'(DB) MySql > Select sql' 카테고리의 다른 글

mysql rank 구하기 (동일등수) (1)	2017.01.13
Group 별로 상위 n개의 레코드 얻기 (0)	2017.01.12
mysql Rownum 구현하기 (0)	2017.01.12

Posted by 농부지기

,

mysql rank 구하기 (동일등수)

(DB) MySql/Select sql 2017. 1. 13. 23:47

** mysql rank 구하기 (동일등수) **

첫번째 방법

- 테이블을 3번 사용해서 구하기 (가장 무식한 방법)

SELECT a.player_seq , (SELECT count(0) FROM hanjul.TPlayerInGame s1 WHERE s1.part_rope_cnt=(SELECT MAX(s2.part_rope_cnt) FROM hanjul.TPlayerInGame s2 WHERE s1.contest_yy = s2.contest_yy AND s1.contest_seq = s2.contest_seq AND s1.player_seq = s2.player_seq AND s1.game_cd = s2.game_cd AND s1.game_part_cd = s2.game_part_cd ) AND s1.part_rope_cnt > a.max_part_rope_cnt AND s1.game_part_cd = a.game_part_cd AND s1.contest_yy = a.contest_yy AND s1.contest_seq = a.contest_seq AND s1.game_cd = a.game_cd AND s1.part_rope_cnt > 0 ) + 1 AS rank FROM (SELECT contest_yy, contest_seq, game_cd, player_seq, game_part_cd , MAX(part_rope_cnt) AS max_part_rope_cnt FROM hanjul.TPlayerInGame WHERE contest_yy = '2016' AND contest_seq = 11 AND game_cd = '0010' AND part_rope_cnt > 0 GROUP BY player_seq, game_part_cd) a

----------------------------------
반 : 이름 : 점수 : 등수
1반 강감찬 100 1
1반   이순신 96 2
1반   홍길동 96 2
1반   김유신 90 4
----------------------------------
2반 말똥고리 80 1
2반 장수말벌 70 2
2반 꿀벌 60 3
2반 나비 60 3
------------------------------------

두번째 방법

- 테이블을 2번 사용해서 구하기 - 속도 개선

SELECT a.gb, a.nm, a.jum , COUNT(b.gb) + 1 rk FROM table a LEFT OUTER JOIN table b ON a.gb = b.gb AND a.jum < b.jum GROUP BY a.gb, a.nm, a.jum ORDER BY gb, rk, nm

세번째 방법

- 변수를 활용해서 구하기 - 속도 짱

SELECT gb, nm, jum , @rn := CASE WHEN @gb != gb THEN 1 ELSE @rn + 1 END rn , @rk := CASE WHEN @rn = 1 THEN 1 ELSE CASE WHEN @jum = jum THEN @rk ELSE @rn END END rk , @gb := gb , @jum := jum FROM (SELECT gb, nm, jum FROM table ORDER BY gb, jum DESC, nm) a , (SELECT @gb := '', @jum := 0, @rn := 0, @rk = 0) b --ORDER BY gb, 3 : 을 하면 역순으로 등수가 부여 됨

저작자표시

'(DB) MySql > Select sql' 카테고리의 다른 글

mysql rank 구하기 (팀이 존재 하는 동일등수) (0)	2017.01.16
Group 별로 상위 n개의 레코드 얻기 (0)	2017.01.12
mysql Rownum 구현하기 (0)	2017.01.12

Posted by 농부지기

,

Group 별로 상위 n개의 레코드 얻기

(DB) MySql/Select sql 2017. 1. 12. 23:58

** Group 별로 상위 n개의 레코드 얻기 **

Step 1: getting the top

We already know how to get a single column's value for the top country, as presented in the aforementioned post:

SELECT Continent
     , SUBSTRING_INDEX(ROUP_CONCAT(Name ORDER BY SurfaceArea DESC),',', 1) AS Name
  FROM Country
 GROUP BY Continent;
+---------------+--------------------+
| Continent     | Name               |
+---------------+--------------------+
| Asia          | China              |
| Europe        | Russian Federation |
| North America | Canada             |
| Africa        | Sudan              |
| Oceania       | Australia          |
| Antarctica    | Antarctica         |
| South America | Brazil             |
+---------------+--------------------+

Step 2: adding columns

This part is easy: just throw in the rest of the columns (again, only indicating the top country in each continent)

SELECT
 Continent,
 SUBSTRING_INDEX(
   GROUP_CONCAT(Name ORDER BY SurfaceArea DESC),
   ',', 1) AS Name,
 SUBSTRING_INDEX(
   GROUP_CONCAT(SurfaceArea ORDER BY SurfaceArea DESC),
   ',', 1) AS SurfaceArea,
 SUBSTRING_INDEX(
   GROUP_CONCAT(Population ORDER BY SurfaceArea DESC),
   ',', 1) AS Population
FROM
 Country
GROUP BY
 Continent
;

+---------------+--------------------+-------------+------------+
| Continent     | Name               | SurfaceArea | Population |
+---------------+--------------------+-------------+------------+
| Asia          | China              | 9572900.00  | 1277558000 |
| Europe        | Russian Federation | 17075400.00 | 146934000  |
| North America | Canada             | 9970610.00  | 31147000   |
| Africa        | Sudan              | 2505813.00  | 29490000   |
| Oceania       | Australia          | 7741220.00  | 18886000   |
| Antarctica    | Antarctica         | 13120000.00 | 0          |
| South America | Brazil             | 8547403.00  | 170115000  |
+---------------+--------------------+-------------+------------+

Step 3: casting

You'll notice that the Population column from this last execution is aligned to the left. This is because it is believed to be a string. The GROUP_CONCAT clause concatenates values in one string, and SUBSTRING_INDEX parses a substring. The same applies to the SurfaceArea column. We'll cast Population as UNSIGNED and SurfaceArea as DECIMAL:

SELECT
  Continent,
  SUBSTRING_INDEX(
    GROUP_CONCAT(Name ORDER BY SurfaceArea DESC),
    ',', 1) AS Name,
  CAST(
    SUBSTRING_INDEX(
      GROUP_CONCAT(SurfaceArea ORDER BY SurfaceArea DESC),
      ',', 1)
    AS DECIMAL(20,2)
    ) AS SurfaceArea,
  CAST(
    SUBSTRING_INDEX(
      GROUP_CONCAT(Population ORDER BY SurfaceArea DESC),
      ',', 1)
    AS UNSIGNED
    ) AS Population
FROM
 Country
GROUP BY
 Continent
;
+---------------+--------------------+-------------+------------+
| Continent     | Name               | SurfaceArea | Population |
+---------------+--------------------+-------------+------------+
| Asia          | China              |  9572900.00 | 1277558000 |
| Europe        | Russian Federation | 17075400.00 |  146934000 |
| North America | Canada             |  9970610.00 |   31147000 |
| Africa        | Sudan              |  2505813.00 |   29490000 |
| Oceania       | Australia          |  7741220.00 |   18886000 |
| Antarctica    | Antarctica         | 13120000.00 |          0 |
| South America | Brazil             |  8547403.00 |  170115000 |
+---------------+--------------------+-------------+------------+

Step 4: top n records

It's time to use string walking. Examples for string walking (described in the excellent SQL Cookbook) can be found here, here and here. We'll be using a numbers table: a simple table which lists ascending integer numbers. For example, you can use the following:

DROP TABLE IF EXISTS `tinyint_asc`;

CREATE TABLE `tinyint_asc` (
 `value` tinyint(3) unsigned NOT NULL default '0',
 PRIMARY KEY (value)
) ;

INSERT INTO `tinyint_asc` VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(44),(45),(46),(47),(48),(49),(50),(51),(52),(53),(54),(55),(56),(57),(58),(59),(60),(61),(62),(63),(64),(65),(66),(67),(68),(69),(70),(71),(72),(73),(74),(75),(76),(77),(78),(79),(80),(81),(82),(83),(84),(85),(86),(87),(88),(89),(90),(91),(92),(93),(94),(95),(96),(97),(98),(99),(100),(101),(102),(103),(104),(105),(106),(107),(108),(109),(110),(111),(112),(113),(114),(115),(116),(117),(118),(119),(120),(121),(122),(123),(124),(125),(126),(127),(128),(129),(130),(131),(132),(133),(134),(135),(136),(137),(138),(139),(140),(141),(142),(143),(144),(145),(146),(147),(148),(149),(150),(151),(152),(153),(154),(155),(156),(157),(158),(159),(160),(161),(162),(163),(164),(165),(166),(167),(168),(169),(170),(171),(172),(173),(174),(175),(176),(177),(178),(179),(180),(181),(182),(183),(184),(185),(186),(187),(188),(189),(190),(191),(192),(193),(194),(195),(196),(197),(198),(199),(200),(201),(202),(203),(204),(205),(206),(207),(208),(209),(210),(211),(212),(213),(214),(215),(216),(217),(218),(219),(220),(221),(222),(223),(224),(225),(226),(227),(228),(229),(230),(231),(232),(233),(234),(235),(236),(237),(238),(239),(240),(241),(242),(243),(244),(245),(246),(247),(248),(249),(250),(251),(252),(253),(254),(255);

The trick is to apply the same technique as used above, not for a single row, but for several rows. Here's how to present the top 5 countries:

SELECT
  Continent,
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(
      GROUP_CONCAT(Name ORDER BY SurfaceArea DESC),
      ',', value),
    ',', -1)
    AS Name,
  CAST(
    SUBSTRING_INDEX(
      SUBSTRING_INDEX(
        GROUP_CONCAT(SurfaceArea ORDER BY SurfaceArea DESC),
        ',', value),
      ',', -1)
    AS DECIMAL(20,2)
    ) AS SurfaceArea,
  CAST(
    SUBSTRING_INDEX(
      SUBSTRING_INDEX(
        GROUP_CONCAT(Population ORDER BY SurfaceArea DESC),
        ',', value),
      ',', -1)
    AS UNSIGNED
    ) AS Population
FROM
  Country, tinyint_asc
WHERE
  tinyint_asc.value >= 1 AND tinyint_asc.value <= 5
GROUP BY
  Continent, value
;
+---------------+----------------------------------------------+-------------+------------+
| Continent     | Name                                         | SurfaceArea | Population |
+---------------+----------------------------------------------+-------------+------------+
| Asia          | China                                        |  9572900.00 | 1277558000 |
| Asia          | India                                        |  3287263.00 | 1013662000 |
| Asia          | Kazakstan                                    |  2724900.00 |   16223000 |
| Asia          | Saudi Arabia                                 |  2149690.00 |   21607000 |
| Asia          | Indonesia                                    |  1904569.00 |  212107000 |
| Europe        | Russian Federation                           | 17075400.00 |  146934000 |
| Europe        | Ukraine                                      |   603700.00 |   50456000 |
| Europe        | France                                       |   551500.00 |   59225700 |
| Europe        | Spain                                        |   505992.00 |   39441700 |
| Europe        | Sweden                                       |   449964.00 |    8861400 |
| North America | Canada                                       |  9970610.00 |   31147000 |
| North America | United States                                |  9363520.00 |  278357000 |
| North America | Greenland                                    |  2166090.00 |      56000 |
| North America | Mexico                                       |  1958201.00 |   98881000 |
| North America | Nicaragua                                    |   130000.00 |    5074000 |
| Africa        | Sudan                                        |  2505813.00 |   29490000 |
| Africa        | Algeria                                      |  2381741.00 |   31471000 |
| Africa        | Congo                                        |  2344858.00 |   51654000 |
| Africa        |  The Democratic Republic of the              |  1759540.00 |    5605000 |
| Africa        | Libyan Arab Jamahiriya                       |  1284000.00 |    7651000 |
| Oceania       | Australia                                    |  7741220.00 |   18886000 |
| Oceania       | Papua New Guinea                             |   462840.00 |    4807000 |
| Oceania       | New Zealand                                  |   270534.00 |    3862000 |
| Oceania       | Solomon Islands                              |    28896.00 |     444000 |
| Oceania       | New Caledonia                                |    18575.00 |     214000 |
| Antarctica    | Antarctica                                   | 13120000.00 |          0 |
| Antarctica    | French Southern territories                  |     7780.00 |          0 |
| Antarctica    | South Georgia and the South Sandwich Islands |     3903.00 |          0 |
| Antarctica    | Heard Island and McDonald Islands            |      359.00 |          0 |
| Antarctica    | Bouvet Island                                |       59.00 |          0 |
| South America | Brazil                                       |  8547403.00 |  170115000 |
| South America | Argentina                                    |  2780400.00 |   37032000 |
| South America | Peru                                         |  1285216.00 |   25662000 |
| South America | Colombia                                     |  1138914.00 |   42321000 |
| South America | Bolivia                                      |  1098581.00 |    8329000 |
+---------------+----------------------------------------------+-------------+------------+

저작자표시

'(DB) MySql > Select sql' 카테고리의 다른 글

mysql rank 구하기 (팀이 존재 하는 동일등수) (0)	2017.01.16
mysql rank 구하기 (동일등수) (1)	2017.01.13
mysql Rownum 구현하기 (0)	2017.01.12

Posted by 농부지기

,

mysql Rownum 구현하기

(DB) MySql/Select sql 2017. 1. 12. 23:38

** Rownum 구현하기**

1. sql

SELECT @rnum := @rnum + 1

FROM (SELECT @rnum := 0) rn

2. SELECT @rnum := 0 : from 절에 @rnum 이라는 변수 생성 및 초기화

@rnum := @rnum + 1 : 컬럼 절에 @rnum 을 add한다.

3. set구문을 사용하여 rownum

SET @rnum := 0;

SELECT @rnum := @rnum + 1

FROM 테이블명;

4. FROM절에서 초기화

SELECT @rnum := @rnum + 1

FROM (SELECT @rnum := 0) rn

5. WHERE절에서 초기화

SELECT @rnum := @rnum + 1

FROM 테이블명

WHERE (@rnum := 0) = 0;

저작자표시

'(DB) MySql > Select sql' 카테고리의 다른 글

mysql rank 구하기 (팀이 존재 하는 동일등수) (0)	2017.01.16
mysql rank 구하기 (동일등수) (1)	2017.01.13
Group 별로 상위 n개의 레코드 얻기 (0)	2017.01.12

Posted by 농부지기

,

농부지기

'(DB) MySql/Select sql'에 해당되는 글 4건

mysql rank 구하기 (팀이 존재 하는 동일등수)

'(DB) MySql > Select sql' 카테고리의 다른 글

mysql rank 구하기 (동일등수)

'(DB) MySql > Select sql' 카테고리의 다른 글

Group 별로 상위 n개의 레코드 얻기

Step 1: getting the top

Step 2: adding columns

Step 3: casting

Step 4: top n records

'(DB) MySql > Select sql' 카테고리의 다른 글

mysql Rownum 구현하기

'(DB) MySql > Select sql' 카테고리의 다른 글

카테고리

태그목록

최근에 올라온 글

최근에 달린 댓글

최근에 받은 트랙백

글 보관함

달력

링크

티스토리툴바