本笔记为阿里云天池龙珠计划SQL训练营的学习内容,链接为:https://tianchi.aliyun.com/specials/promotion/aicampsql;
-- 练习题1:
-- 数据来源:https://tianchi.aliyun.com/dataset/dataDetail?dataId=1074
--
-- 请使用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
SELECT md.TICKER_SYMBOL,
md.END_DATE,
md.CLOSE_PRICE,
ist.TICKER_SYMBOL,
ist.END_DATE,
ist.T_REVENUE,
ist.T_COGS,
ist.N_INCOME,
co.TICKER_SYMBOL,
co.INDIC_NAME_EN,
co.END_DATE,
co.`VALUE`
FROM `market data` md
INNER JOIN `income statement` ist
ON md.TICKER_SYMBOL = ist.TICKER_SYMBOL AND md.END_DATE = ist.END_DATE
INNER JOIN `company operating` co
ON md.TICKER_SYMBOL = co.TICKER_SYMBOL AND md.END_DATE = co.END_DATE
WHERE md.TICKER_SYMBOL in (600383,600048)
-- 练习题2:
-- 数据来源:https://tianchi.aliyun.com/dataset/dataDetail?dataId=44
--
-- 请使用 Wine Quality Data 数据集《winequality-red.csv》,找出 pH=3.03的所有红葡萄酒,然后,对其 citric acid 进行中式排名(相同排名的下一个名次应该是下一个连续的整数值。换句话说,名次之间不应该有“间隔”)
SELECT *,DENSE_RANK() over(ORDER BY `citric acid`)
FROM `winequality-red`
WHERE ph=3.03