My_SQL天池训练打卡6

MySQL_Task_06

练习题一

没看懂

练习题二

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

练习题三

 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
 ​
 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

练习题四

 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

练习五

 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';
 ​
 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

练习六

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

练习七

 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