SQL学习笔记_Aliyun6

SQL学习笔记_Aliyun6

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

• 如何下载并使用MySQL
• 如何使用DataGrip
• 如何执行sql脚本
• 如何改变编码utf8mb4

https://blog.youkuaiyun.com/qq_52153640/article/details/126128207

http://t.zoukankan.com/kex1n-p-2286504.html

https://www.cnblogs.com/mt0622/p/16493587.html

https://blog.youkuaiyun.com/QAZJOU/article/details/123534496

练习1

注：这个我一直都显示搜寻结果为0个

SELECT DISTINCT
    MD.*,
    CO.INDIC_NAME_EN,
    CO.VALUE,
    incom.N_INCOME,
    incom.T_COGS,
    incom.T_REVENUE 
FROM
    ( SELECT TICKER_SYMBOL, END_DATE, CLOSE_PRICE FROM `market data` WHERE TICKER_SYMBOL = 600383 OR TICKER_SYMBOL = 600048  ) MD # market data
    LEFT JOIN
    ( SELECT TICKER_SYMBOL, INDIC_NAME_EN, END_DATE, VALUE FROM `company operating` WHERE TICKER_SYMBOL = 600383 OR TICKER_SYMBOL = 600048  ) CO ON MD.TICKER_SYMBOL = CO.TICKER_SYMBOL AND MD.END_DATE = CO.END_DATE # company operating
    LEFT JOIN
    ( SELECT TICKER_SYMBOL, END_DATE, T_REVENUE, T_COGS, N_INCOME FROM `income statement` WHERE TICKER_SYMBOL = 600383 OR TICKER_SYMBOL = 600048 ) incom ON CO.TICKER_SYMBOL = incom.TICKER_SYMBOL AND CO.END_DATE = incom.END_DATE # income statement
ORDER BY
    MD.TICKER_SYMBOL,
    MD.END_DATE

练习2&练习6

这两个关于白酒排序的比较简单

SELECT
    pH,
    `citric acid`,
    RANK() OVER ( ORDER BY `citric acid` ) AS rank_red 
FROM
    `winequality-red` 
WHERE
    pH = 3.03;
    

SELECT pH,
`residual sugar`,
RANK() OVER (ORDER BY `residual sugar`) AS rankn
FROM `winequality-white`
WHERE pH= 3.63;

练习3

SELECT Merchant_id,
SUM(SUBSTRING_INDEX(`Discount_rate`,':',-1)) AS discount_amount
FROM ccf_offline_stage1_test_revised
WHERE Date_received BETWEEN '2016-07-01' AND '2016-07-31'
GROUP BY Merchant_id
ORDER BY discount_amount DESC
LIMIT 1;
-- 发放优惠券张数最多的商家
SELECT Merchant_id,COUNT(1) AS cnt
FROM ccf_offline_stage1_test_revised
WHERE Date_received BETWEEN '2016-07-01' AND '2016-07-31'
GROUP BY Merchant_id
ORDER BY cnt DESC
LIMIT 1;

练习4

-- 2015年⽤电最⾼峰是发⽣在哪⽉
SELECT PERIOD_DATE,
MAX(DATA_VALUE) FianlValue
FROM `macro industry`
WHERE INDIC_ID = '2020101522'
AND YEAR(PERIOD_DATE) = 2015
GROUP BY PERIOD_DATE
ORDER BY FianlValue DESC
LIMIT 1;
-- 并且相⽐去年同期增⻓/减少了多少个百分⽐？
SELECT BaseData.*,
(BaseData.FianlValue - YoY.FianlValue) / YoY.FianlValue YoY
FROM (SELECT PERIOD_DATE,
MAX(DATA_VALUE) FianlValue
FROM `macro industry`
WHERE INDIC_ID = '2020101522'
AND YEAR(PERIOD_DATE) = 2015
GROUP BY PERIOD_DATE
ORDER BY FianlValue DESC
LIMIT 1) BaseData
LEFT JOIN -- YOY
(SELECT PERIOD_DATE,
MAX(DATA_VALUE) FianlValue
FROM `macro industry`
WHERE INDIC_ID = '2020101522'
AND YEAR(PERIOD_DATE) = 2014
GROUP BY PERIOD_DATE ) YoY
ON YEAR(BaseData.PERIOD_DATE) = YEAR(YoY.PERIOD_DATE) + 1
AND MONTH(BaseData.PERIOD_DATE) = MONTH(YoY.PERIOD_DATE);

练习5

-- 2016年6⽉期间，线上总体优惠券弃⽤率为多少？
SELECT SUM(CASE WHEN Date='0000-00-00' AND Coupon_id IS NOT NULL
THEN 1
ELSE 0
END) /
SUM(CASE WHEN Coupon_id IS NOT NULL
THEN 1
ELSE 0
END) AS discard_rate
FROM ccf_online_stage1_train
WHERE Date_received BETWEEN '2016-06-01' AND '2016-06-30';
-- 2016年6⽉期间，优惠券弃⽤率最⾼的商家？
SELECT Merchant_id,
SUM(CASE WHEN Date = '0000-00-00' AND Coupon_id IS NOT NULL
THEN 1
ELSE 0
END) /
SUM(CASE WHEN Coupon_id IS NOT NULL
THEN 1
ELSE 0
END) AS discard_rate
FROM ccf_online_stage1_train
WHERE Date_received BETWEEN '2016-06-01' AND '2016-06-30'
GROUP BY Merchant_id
ORDER BY discard_rate DESC
LIMIT 1;

练习7

1. 截至市值最⼤的三个⾏业是->选出行业以及市值

SELECT TYPE_NAME_CN,
SUM(MARKET_VALUE)
FROM `market data`
WHERE YEAR(END_DATE) = '2018-12-31'
GROUP BY TYPE_NAME_CN
ORDER BY SUM(MARKET_VALUE) DESC
LIMIT 3

2. 利用关联子查询以及窗口查询找到三个行业里市值最大的三个公司

-- 这三个⾏业⾥市值最⼤的三个公司是哪些？
SELECT BaseData.TYPE_NAME_CN,
BaseData.TICKER_SYMBOL
FROM (SELECT TYPE_NAME_CN,
TICKER_SYMBOL,
MARKET_VALUE,
ROW_NUMBER() OVER(PARTITION BY TYPE_NAME_CN ORDER BY MARKET_VALUE)
CompanyRanking
FROM `market data` ) BaseData
LEFT JOIN
( SELECT TYPE_NAME_CN,
SUM(MARKET_VALUE)
FROM `market data`
WHERE YEAR(END_DATE) = '2018-12-31'
GROUP BY TYPE_NAME_CN
ORDER BY SUM(MARKET_VALUE) DESC
LIMIT 3 ) top3Type
ON BaseData.TYPE_NAME_CN = top3Type.TYPE_NAME_CN
WHERE CompanyRanking <= 3
AND top3Type.TYPE_NAME_CN IS NOT NULL

练习8

SELECT User_id,
SUM(couponCount) couponCount
FROM (SELECT User_id,
count(*) couponCount
FROM `ccf_online_stage1_train`
WHERE (Date != 'null' AND Coupon_id != 'null')
AND (LEFT(DATE,4)=2016 )
GROUP BY User_id
UNION ALL
SELECT User_id,
COUNT(*) couponCount
FROM `ccf_offline_stage1_train`
WHERE (Date != 'null' AND Coupon_id != 'null')
AND (LEFT(DATE,4)=2016 )
GROUP BY User_id ) BaseData
GROUP BY User_id
ORDER BY SUM(couponCount) DESC
LIMIT 1

练习9

找出在数据集所有年份中，按季度统计，⽩云机场旅客吞吐量最⾼的那⼀季度对应的净利润是多少？
（注意，是单季度对应的净利润，⾮累计净利润。）

SELECT *
FROM (SELECT TICKER_SYMBOL,
YEAR(END_DATE) Year,
QUARTER(END_DATE) QUARTER,
SUM(VALUE) Amount
FROM `company operating`
WHERE INDIC_NAME_EN = 'Baiyun Airport:Passenger throughput'
GROUP BY TICKER_SYMBOL,YEAR(END_DATE),QUARTER(END_DATE)
ORDER BY SUM(VALUE) DESC
LIMIT 1 ) BaseData
LEFT JOIN -- income statement
(SELECT TICKER_SYMBOL,
YEAR(END_DATE) Year,
QUARTER(END_DATE) QUARTER,
SUM(N_INCOME) Amount
FROM `income statement`
GROUP BY TICKER_SYMBOL,YEAR(END_DATE),QUARTER(END_DATE) ) Income
ON BaseData.TICKER_SYMBOL = Income.TICKER_SYMBOL

结果只有一个虽然显示了41条查询结果

练习10

SELECT Merchant_id,
SUM(discount_amount) discount_amount
FROM (SELECT Merchant_id,
SUM(SUBSTRING_INDEX(`Discount_rate`,':',-1)) AS discount_amount
FROM `ccf_online_stage1_train`
WHERE (Date != 'null' AND Coupon_id != 'null')
AND (LEFT(DATE,4)=2016 )
AND MID(DATE,5,2) = '06'
GROUP BY Merchant_id
UNION ALL
SELECT Merchant_id,
SUM(SUBSTRING_INDEX(`Discount_rate`,':',-1)) AS discount_amount
FROM `ccf_offline_stage1_train`
WHERE (Date != 'null' AND Coupon_id != 'null')
AND (LEFT(DATE,4)=2016 )
AND MID(DATE,5,2) = '06'
GROUP BY Merchant_id ) BaseData
GROUP BY Merchant_id
ORDER BY SUM(discount_amount) DESC
LIMIT 1