阿里天池SQL训练营学习笔记6

最新推荐文章于 2025-12-16 09:18:35 发布

原创最新推荐文章于 2025-12-16 09:18:35 发布 · 268 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #学习 #数据库

该笔记介绍了如何使用SQL结合三个数据集——IncomeStatement、MarketData和CompanyOperating——来获取特定股票(TICKER_SYMBOL为600383和600048)的信息。内容涉及数据导入数据库、表连接操作，以及选取特定字段展示，包括TICKER_SYMBOL、END_DATE、财务数据和股价等信息。

本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql；

文章目录

SQL练习题

SQL练习题

请使⽤A股上市公司季度营收预测数据集《Income Statement.xls》和《Company Operating.xlsx》和
《Market Data.xlsx》，以Market Data为主表，将三张表中的TICKER_SYMBOL为600383和600048的
信息合并在⼀起。只需要显示以下字段。
表名字段名
Income Statement TICKER_SYMBOL
Income Statement END_DATE
Income Statement T_REVENUE
Income Statement T_COGS
Income Statement N_INCOME
Market Data TICKER_SYMBOL
Market Data END_DATE_
Market Data CLOSE_PRICE
Company Operating TICKER_SYMBOL
Company Operating INDIC_NAME_EN
Company Operating END_DATE
Company Operating VALUE

答案及思路
将数据表导⼊数据库，根据表结构和字段含义，Company Operating表使⽤sheet-EN(使⽤CN也可以)，
Income Statement表使⽤sheet-General Business，Market Data使⽤sheet-Data。

SELECT MarketData.*,
OperatingData.INDIC_NAME_EN,
OperatingData.VALUE,
IncomeStatement.N_INCOME,
IncomeStatement.T_COGS,
IncomeStatement.T_REVENUE
FROM (
SELECT TICKER_SYMBOL,
END_DATE,
CLOSE_PRICE
FROM market data
WHERE TICKER_SYMBOL IN (‘600383’,‘600048’) ) MarketData
LEFT JOIN – operating data
(SELECT TICKER_SYMBOL,
INDIC_NAME_EN,
END_DATE,
VALUE
FROM company operating
WHERE TICKER_SYMBOL IN (‘600383’,‘600048’) ) OperatingData
ON MarketData.TICKER_SYMBOL = OperatingData.TICKER_SYMBOL
AND MarketData.END_DATE = OperatingData.END_DATE
LEFT JOIN – income statement
(SELECT DISTINCT TICKER_SYMBOL,
END_DATE,
T_REVENUE,
T_COGS,
N_INCOME
FROM income statement
WHERE TICKER_SYMBOL IN (‘600383’,‘600048’) ) IncomeStatement
ON MarketData.TICKER_SYMBOL = IncomeStatement.TICKER_SYMBOL
AND MarketData.END_DATE = IncomeStatement.END_DATE
ORDER BY MarketData.TICKER_SYMBOL, MarketData.END_DATE