https://www.kaggle.com/datasets/nathanlauga/nba-games/data games.csv 從 2004 賽季到上一次的所有比賽都會更新日期、球隊和一些詳細資訊，如積分等。 games_details.csv 比賽數據集的詳細資訊，給定比賽的球員的所有統計資訊 players.csv 球員詳細資訊（姓名） ranking.csv 給定一天的 NBA 排名 teams.csv NBA 所有球隊 games.csv 從 2004 賽季到上一次的所有比賽都會更新日期、球隊和一些詳細資訊，如積分等。 =================================== GAME_DATE_EST,比賽日期 GAME_ID,比賽ID GAME_STATUS_TEXT,狀態 HOME_TEAM_ID, VISITOR_TEAM_ID, SEASON,NBA 賽季(年) TEAM_ID_home,主隊 ID PTS_home,主隊得分 FG_PCT_home,主隊投籃命中率 FT_PCT_home,主隊的罰球百分比 FG3_PCT_home,主隊的三分球百分比 AST_home,主隊的助攻 REB_home,主隊籃板 TEAM_ID_away,客隊 ID PTS_away,客隊得分 FG_PCT_away,客隊投籃命中率 FT_PCT_away,客隊的罰球百分比 FG3_PCT_away,客隊的三分球百分比 AST_away,客隊助攻 REB_away,客隊籃板球 HOME_TEAM_WINS,主隊勝否 create table games ( GAME_DATE_EST date, GAME_ID varchar(10), GAME_STATUS_TEXT varchar(10), HOME_TEAM_ID varchar(10), VISITOR_TEAM_ID varchar(10), SEASON varchar(4), TEAM_ID_home varchar(10), PTS_home int, FG_PCT_home decimal(6,4), FT_PCT_home decimal(6,4), FG3_PCT_home decimal(6,4), AST_home int, REB_home int, TEAM_ID_away varchar(10), PTS_away int, FG_PCT_away decimal(6,4), FT_PCT_away decimal(6,4), FG3_PCT_away decimal(6,4), AST_away int, REB_away int, HOME_TEAM_WINS varchar(1), primary key(GAME_ID) ) ********************** Sample Data ********************** 2022-12-22,22200477,Final,1610612740,1610612759,2022,1610612740,126,0.484,0.926,0.382,25,46,1610612759,117,0.478,0.815,0.321,23,44,1 games_details.csv 比賽數據集的詳細資訊，給定比賽的球員的所有統計資訊 =================================== GAME_ID,比賽 ID TEAM_ID,球團的ID TEAM_ABBREVIATION,球團的縮寫 TEAM_CITY,比賽所在的城市 PLAYER_ID,球員的ID PLAYER_NAME,球員姓名 NICKNAME,昵稱 START_POSITION,先發位置 COMMENT,評論 MIN,上場分鐘數 FGM,射門得分 FGA,投籃出手數 FG_PCT,投籃命中率 FG3M,三分球命中 FG3A,三分球出手數 FG3_PCT,三分球命中率 FTM,罰球命中 FTA,罰球出手數 FT_PCT,罰球命中率 OREB,進攻籃板 DREB,防守籃板 REB,籃板球 AST,助攻 STL,抄截 BLK,封蓋(火鍋) TO,失誤(Turnovers) PF,個人犯規(Personnal Foul) PTS,得分(Number of points scored by the player) PLUS_MINUS,加-減 create table games_details ( GAME_ID varchar(10), TEAM_ID varchar(10), TEAM_ABBREVIATION varchar(20), TEAM_CITY varchar(100), PLAYER_ID varchar(10), PLAYER_NAME varchar(100), NICKNAME varchar(30), START_POSITION varchar(10), COMMENT varchar(100), MIN decimal(6,4), FGM int, FGA int, FG_PCT decimal(6,4), FG3M int, FG3A int, FG3_PCT decimal(6,4), FTM int, FTA int, FT_PCT decimal(6,4), OREB int, DREB int, REB int, AST int, STL int, BLK int, `TO` int, PF int, PTS int, PLUS_MINUS decimal(6,4), primary key (GAME_ID,TEAM_ID,PLAYER_ID) ) ********************** Sample Data ********************** 22200477,1610612759,SAS,San Antonio,1629641,Romeo Langford,Romeo,F,,18:06,1.0,1.0,1.0,0.0,0.0,0.0,0.0,0.0,0.0,1.0,1.0,2.0,0.0,1.0,0.0,2.0,5.0,2.0,-2.0 -- create table game_full_record ( gtype varchar(10), GAME_ID varchar(10), GAME_DATE_EST date, SEASON varchar(10), TEAM_ID varchar(10), team_name varchar(100), team_nick varchar(20), PLAYER_ID varchar(10), PLAYER_NAME varchar(100), NICKNAME varchar(20), MIN decimal(6,4), FGM int, FGA int, FG_PCT decimal(6,4), FG3M int, FG3A int, FG3_PCT decimal(6,4), FTM int, FTA int, FT_PCT decimal(6,4), OREB int, DREB int, REB int, AST int, STL int, BLK int, `TO` int, PF int, PTS int, primary key (GAME_ID,TEAM_ID,PLAYER_ID) ) -- create table game_team_record ( gtype varchar(10), GAME_ID varchar(10), GAME_DATE_EST date, SEASON varchar(10), TEAM_ID varchar(10), team_name varchar(100), team_nick varchar(20), -- team score PTS int, -- 得分 FG_PCT decimal(6,4), -- 投籃命中率 FT_PCT decimal(6,4), -- 罰球百分比 FG3_PCT decimal(6,4), -- 三分球百分比 AST int, -- 助攻 REB int, -- 籃板 primary key (GAME_ID,TEAM_ID) ) /* DW insert INTO dw_player_statistics ( team_id,team_nick,player_id,PLAYER_NAME,SEASON,YM, MIN_SUM, FGM_SUM, FGA_SUM, FG3M_SUM, FG3A_SUM, FTM_SUM, FTA_SUM, OREB_SUM, DREB_SUM, REB_SUM, AST_SUM, STL_SUM, BLK_SUM, TO_SUM, PF_SUM, PTS_SUM ) select team_id,team_nick,player_id,PLAYER_NAME,SEASON,date_format(GAME_DATE_EST,"%Y%m") YM, sum(MIN) MIN_SUM, sum(FGM) FGM_SUM, sum(FGA) FGA_SUM, sum(FG3M) FG3M_SUM, sum(FG3A) FG3A_SUM, sum(FTM) FTM_SUM, sum(FTA) FTA_SUM, sum(OREB) OREB_SUM, sum(DREB) DREB_SUM, sum(REB) REB_SUM, sum(AST) AST_SUM, sum(STL) STL_SUM, sum(BLK) BLK_SUM, sum(`TO`) TO_SUM, sum(PF) PF_SUM, sum(PTS) PTS_SUM from game_full_record where 1=1 group by team_id,team_nick,player_id,PLAYER_NAME,SEASON,YM order by 1,3,5,6 */ create table dw_player_statistics ( TEAM_ID varchar(10), team_nick varchar(20), PLAYER_ID varchar(10), PLAYER_NAME varchar(100), SEASON varchar(10), YM varchar(6), MIN_SUM decimal(20,4), FGM_SUM int, FGA_SUM int, FG3M_SUM int, FG3A_SUM int, FTM_SUM int, FTA_SUM int, OREB_SUM int, DREB_SUM int, REB_SUM int, AST_SUM int, STL_SUM int, BLK_SUM int, TO_SUM int, PF_SUM int, PTS_SUM int, primary key (TEAM_ID,PLAYER_ID,SEASON,YM) ) insert into dw_team_statistics ( TEAM_ID,team_nick,SEASON,YM, PTS_SUM, AST_SUM, REB_SUM ) select TEAM_ID,team_nick,SEASON,date_format(GAME_DATE_EST,"%Y%m") YM, sum(PTS),sum(AST),sum(REB) from game_team_record where 1=1 group by TEAM_ID,team_nick,SEASON,YM order by 1,3,4 -- create table dw_team_statistics ( TEAM_ID varchar(10), team_nick varchar(20), SEASON varchar(10), YM varchar(10), -- PTS_SUM int, -- 得分 AST_SUM int, -- 助攻 REB_SUM int, -- 籃板 primary key (TEAM_ID,YM) ) players.csv 球員詳細資訊（姓名） =================================== PLAYER_NAME,球員姓名 TEAM_ID,球團的ID PLAYER_ID,球員的ID SEASON,球季(年) create table players ( PLAYER_NAME varchar(60), TEAM_ID varchar(10), PLAYER_ID varchar(10), SEASON varchar(10), primary key (TEAM_ID,PLAYER_ID,SEASON) ) ********************** Sample Data ********************** Royce O'Neale,1610612762,1626220,2019 Bojan Bogdanovic,1610612762,202711,2019 teams.csv NBA 所有球隊 =================================== LEAGUE_ID,聯賽 ID TEAM_ID,球團的ID MIN_YEAR,球隊進入NBA總冠軍的最低年份 MAX_YEAR,球隊進入NBA總冠軍的最大年份 ABBREVIATION,球團名稱的縮寫 NICKNAME,球團的昵稱 YEARFOUNDED,成立年份 CITY,球團所在城市 ARENA,球隊體育場 ARENACAPACITY,體育場的容量 OWNER,球團的擁有者 GENERALMANAGER,總經理 HEADCOACH,主教練 DLEAGUEAFFILIATION,聯盟隸屬關係 create table teams ( LEAGUE_ID varchar(10), TEAM_ID varchar(10), MIN_YEAR varchar(10), MAX_YEAR varchar(10), ABBREVIATION varchar(100), NICKNAME varchar(100), YEARFOUNDED varchar(10), CITY varchar(100), ARENA varchar(100), ARENACAPACITY int, OWNER varchar(100), GENERALMANAGER varchar(100), HEADCOACH varchar(100), DLEAGUEAFFILIATION varchar(100), primary key (TEAM_ID) ) ********************** Sample Data ********************** 00,1610612737,1949,2019,ATL,Hawks,1949,Atlanta,State Farm Arena,18729,Tony Ressler,Travis Schlenk,Lloyd Pierce,Erie Bayhawks /* 補充章節 */ 1.Map https://raw.githubusercontent.com/ronnywang/twgeojson/refs/heads/master/twcounty2010.json 2.示範用 Tableau 的 table 製作 dashboard 3.dashboard 增加 link 的範例及說明 4.