[9주차] 팀스터디노트

이번주 팀스터디내용은 SQL 테스트를 위한 코드 리뷰이다.

팀원분이 공유한 코드

#angela Park���� Ǯ��
SELECT m.member_name, r.review_text, DATE_FORMAT(r.review_date, '%Y-%m-%d') review_date
FROM REST_REVIEW r LEFT JOIN MEMBER_PROFILE m ON r.member_id = m.member_id,
    (
    select member_id
    FROM REST_REVIEW r
    GROUP BY member_id
    HAVING COUNT(review_id) = 
        (SELECT MAX(cnt)
        FROM (SELECT COUNT(*) cnt FROM REST_REVIEW GROUP BY member_id) tb1)
    ) tb2
where tb2.member_id = m.memeber_id
ORDER BY 3 ASC, 2 ASC


#�ʺ� 1���� �糪�̴��� Ǯ��
SELECT m.member_name, r.review_text, DATE_FORMAT(r.review_date, '%Y-%m-%d') review_date
from MEMBER_PROFILE m inner join REST_REVIEW r on m.member_id = r.member_id
where m.member_id =
(select member_id from REST_REVIEW group by memeber_id order by count(review id) desc limit 1)
order by review_date, review_text

내가 공유한 코드

• Kaggle 코드리뷰
• European Soccer Database

sqlite 파일을 다운받아서 데이터분석 진행

# improts 

import numpy as np
import pandas as pd
import sqlite3
import matplotlib.pyplot as plt

database = 'database.sqlite'

conn = sqlite3.connect(database)

tables = pd.read_sql("""SELECT *
                        FROM sqlite_master
                        WHERE type='table';""", conn)
tables

countries = pd.read_sql("""SELECT *
                        FROM Country;""", conn)
countries
---------------------------------------------------------
leagues = pd.read_sql("""SELECT *
                        FROM League
                        JOIN Country ON Country.id = League.country_id;""", conn)
leagues
---------------------------------------------------------
teams = pd.read_sql("""SELECT *
                        FROM Team
                        ORDER BY team_long_name
                        LIMIT 10;""", conn)
teams
---------------------------------------------------------

detailed_matches = pd.read_sql("""SELECT Match.id, 
                                        Country.name AS country_name, 
                                        League.name AS league_name, 
                                        season, 
                                        stage, 
                                        date,
                                        HT.team_long_name AS  home_team,
                                        AT.team_long_name AS away_team,
                                        home_team_goal, 
                                        away_team_goal                                        
                                FROM Match
                                JOIN Country on Country.id = Match.country_id
                                JOIN League on League.id = Match.league_id
                                LEFT JOIN Team AS HT on HT.team_api_id = Match.home_team_api_id
                                LEFT JOIN Team AS AT on AT.team_api_id = Match.away_team_api_id
                                WHERE country_name = 'Spain'
                                ORDER by date
                                LIMIT 10;""", conn)
detailed_matches

leages_by_season = pd.read_sql("""SELECT Country.name AS country_name, 
                                        League.name AS league_name, 
                                        season,
                                        count(distinct stage) AS number_of_stages,
                                        count(distinct HT.team_long_name) AS number_of_teams,
                                        avg(home_team_goal) AS avg_home_team_scors, 
                                        avg(away_team_goal) AS avg_away_team_goals, 
                                        avg(home_team_goal-away_team_goal) AS avg_goal_dif, 
                                        avg(home_team_goal+away_team_goal) AS avg_goals, 
                                        sum(home_team_goal+away_team_goal) AS total_goals                                       
                                FROM Match
                                JOIN Country on Country.id = Match.country_id
                                JOIN League on League.id = Match.league_id
                                LEFT JOIN Team AS HT on HT.team_api_id = Match.home_team_api_id
                                LEFT JOIN Team AS AT on AT.team_api_id = Match.away_team_api_id
                                WHERE country_name in ('Spain', 'Germany', 'France', 'Italy', 'England')
                                GROUP BY Country.name, League.name, season
                                HAVING count(distinct stage) > 10
                                ORDER BY Country.name, League.name, season DESC
                                ;""", conn)
leages_by_season