数据库学习06

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

01数据来源：数据集-阿里云天池

SELECT 
I.TICKER_SYMBOL, I.END_DATE, I.T_REVENUE, I.T_COGS, I.N_INCOME,
MD.TICKER_SYMBOL, MD.END_DATE, MD.CLOSE_PRICE,
CO.TICKER_SYMBOL, CO.INDIC_NAME_EN, CO.END_DATE, CO.VALUE 
FROM `market data` AS MD LEFT JOIN `income statement` AS I 
ON I.TICKER_SYMBOL=MD.TICKER_SYMBOL
LEFT JOIN `company operating` AS CO 
ON CO.TICKER_SYMBOL=MD.TICKER_SYMBOL 
WHERE MD.TICKER_SYMBOL IN (600383,600048);
02数据来源：数据集-阿里云天池

SELECT *, DENSE_RANK() OVER (ORDER BY `citric acid`) 
AS dense_ranking FROM `winequality-red` 
WHERE PH=3.03;

03数据来源：天池新人实战赛o2o优惠券使用预测赛题与数据-天池大赛-阿里云天池

select Merchant_id,SUM(SUBSTRING_INDEX(Discount_rate,':',-1)) 
AS MORE FROM ccf_offline_stage1_test_revised 
WHERE Date_received BETWEEN'2016-07-01' AND '2017-07-31' 
GROUP BY Merchant_id
ORDER BY MORE DESC
LIMIT 1;

select Merchant_id,COUNT(1) AS MORE
FROM ccf_offline_stage1_test_revised 
WHERE Date_received BETWEEN'2016-07-01' AND '2017-07-31' 
GROUP BY Merchant_id
ORDER BY MORE DESC
LIMIT 1;