2021-08-13-TC-12天池AI训练营 SQL Task6 笔记-CSDN博客

本文链接：https://blog.csdn.net/sabian2/article/details/119678799

本文介绍了使用SQL进行数据查询和分析的多个实例，包括从基础的SELECT语句到复杂的联接操作，以及聚合函数的应用。通过实际的股票市场、公司运营、宏观经济和电商交易数据，展示了如何获取特定指标、计算排名、找出最高值、分析趋势和比例等。内容涵盖了数据筛选、聚合、排序和子查询等核心概念，对于理解和提升SQL技能非常有帮助。

摘要由CSDN通过智能技术生成

本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql；
怪不得标注要1天的时间，我的服务器加载那个数据都花了两天
做练习啦
在这里插入图片描述

从小到大，逐步组合

use alia;
select TICKER_SYMBOL,END_DATE,CLOSE_PRICE  from  `market data` as market where TICKER_SYMBOL in (600383,600048);
select TICKER_SYMBOL,END_DATE,T_REVENUE,T_COGS,N_INCOME    from `income statement` as income where TICKER_SYMBOL in (600383,600048);
select  TICKER_SYMBOL,INDIC_NAME_EN,END_DATE,`VALUE`  from `company operating`  where TICKER_SYMBOL in (600383,600048);

select t4.*,t3.INDIC_NAME_EN, t3.`value`
from  (select t1.TICKER_SYMBOL,t1.END_DATE,t1.CLOSE_PRICE , t2.T_REVENUE,t2.T_COGS,t2.N_INCOME --  ,t3.INDIC_NAME_EN, t3.value
from 
`market data`  t1
left join
`income statement` t2
on t1.TICKER_SYMBOL=t2.TICKER_SYMBOL and  t1.END_DATE=t2.END_DATE
-- left join 
-- `company operating` t3
-- on on t1.TICKER_SYMBOL=t3.TICKER_SYMBOL and  t1.END_DATE=t3.END_DATE
where t1.TICKER_SYMBOL in (600383,600048)) t4
left join
`company operating` t3
on t4.TICKER_SYMBOL=t3.TICKER_SYMBOL and  t4.END_DATE=t3.END_DATE
where t4.TICKER_SYMBOL in (600383,600048)

;

在这里插入图片描述

 select *,DENSE_RANK() OVER (ORDER BY `citric acid`) AS 中式排序 
 from `winequality-red`
 where pH='3.03';

在这里插入图片描述

数量最多

select Merchant_id,count(*) as sum from ccf_offline_stage1_test_revised 
 where Date_received like '2016-07-%'
 group by Merchant_id 
 order by sum  desc
 limit 1;

在这里插入图片描述

价值最高

 select Merchant_id,sum(Distance) as sum from ccf_offline_stage1_test_revised 
 where Date_received like '2016-07-%'

 group by Merchant_id
 order by  sum desc
 
limit 1;

在这里插入图片描述

在这里插入图片描述
先看是那个月的

select  sum(DATA_VALUE) as sum,month(PERIOD_DATE) as months from `macro industry` 
where year(PERIOD_DATE) = 2015
group by months
order by sum desc
limit 1;

在这里插入图片描述
分开这两个部分，一个作为15年的值，一个作为where子查询的结果
汇总

select (p1.sum-p2.sum)/p2.sum as 变化率 from
(select  sum(DATA_VALUE) as sum from `macro industry` 
where year(PERIOD_DATE) = 2015
group by month(PERIOD_DATE)
order by sum desc
limit 1) as p1
 ,
 (select sum(DATA_VALUE) as sum  from `macro industry`
where year(PERIOD_DATE) = 2014 and month(PERIOD_DATE)=
			(select  month(PERIOD_DATE) from `macro industry` 
		where year(PERIOD_DATE) = 2015
		group by month(PERIOD_DATE)
		order by sum(DATA_VALUE) desc
		limit 1	)		
) as p2
;

在这里插入图片描述

总体

select 
(select count(*) as count from ccf_online_stage1_train
where year(Date_received)=2016 and  month(Date_received)=6 and Date is null)/
(select count(*) as count from ccf_online_stage1_train
where year(Date_received)=2016 and  month(Date_received)=6
) as 总体弃用率;

在这里插入图片描述

select p1.Merchant_id ,p2.count/p1.count  as 弃用率   from
(select Merchant_id,count(*) as count from ccf_online_stage1_train
where year(Date_received)=2016 and  month(Date_received)=6
group by Merchant_id) as p1
inner join
(select Merchant_id,count(*) as count from ccf_online_stage1_train
where year(Date_received)=2016 and  month(Date_received)=6 and Date is null
group by Merchant_id) as p2
on p1.Merchant_id=p2.Merchant_id
order by 弃用率 desc
;

存在大量弃用率为1的
在这里插入图片描述

 select *,RANK() OVER (ORDER BY `residual sugar`) AS 英式排序 
 from `winequality-white`
 where pH='3.63';

在这里插入图片描述

select TYPE_NAME_EN,sum( MARKET_VALUE) from `market data`
group by TYPE_NAME_EN
order by sum( MARKET_VALUE) desc
limit 3;

在这里插入图片描述

三个行业一个个填进去

select p1.User_id,p2.count+p1.count as sum from
(select User_id ,count(*) as count from ccf_online_stage1_train
where Date_received is not null and Date is not null
group by User_id) as  p1
inner join
(select User_id ,count(*) as count from ccf_offline_stage1_train
where Date_received is not null and Date is not null
group by User_id) as p2
on p1.User_id = p2.User_id
order by sum desc
limit 1
;

在这里插入图片描述

在这里插入图片描述
机场吞吐量

select  year(END_DATE),quarter(END_DATE),sum(VALUE) as tuntu  from   `company operating`
where INDIC_NAME_EN like 'Baiyun Airport%'
group by year(END_DATE),quarter(END_DATE)
order by tuntu desc
limit 1;

在这里插入图片描述

PARTY_ID varchar(255) 
TICKER_SYMBOL varchar(255) 
EXCHANGE_CD varchar(255) 
PUBLISH_DATE varchar(255) 
END_DATE_REP varchar(255) 
END_DATE varchar(255) 
REPORT_TYPE varchar(255) 
FISCAL_PERIOD varchar(255) 
MERGED_FLAG varchar(255) 
T_REVENUE decimal(25,0) 
REVENUE decimal(25,0) 
INT_INCOME decimal(25,0) 
PREM_EARNED decimal(25,0) 
COMMIS_INCOME decimal(25,0) 
SPEC_TOR decimal(25,0) 
ATOR decimal(25,0) 
T_COGS decimal(25,0) 
COGS decimal(25,0) 
INT_EXP decimal(25,0) 
COMMIS_EXP decimal(25,0) 
PREM_REFUND decimal(25,0) 
N_COMPENS_PAYOUT decimal(25,0) 
RESER_INSUR_CONTR decimal(25,0) 
POLICY_DIV_PAYT decimal(25,0) 
REINSUR_EXP decimal(25,0) 
BIZ_TAX_SURCHG decimal(25,0) 
SELL_EXP decimal(25,0) 
ADMIN_EXP decimal(25,0) 
FINAN_EXP decimal(25,0) 
ASSETS_IMPAIR_LOSS decimal(25,0) 
SPEC_TOC decimal(25,0) 
ATOC decimal(25,0) 
F_VALUE_CHG_GAIN decimal(25,0) 
INVEST_INCOME decimal(25,0) 
A_J_INVEST_INCOME decimal(25,0) 
FOREX_GAIN decimal(25,0) 
OTH_EFFECT_OP decimal(25,0) 
ASSETS_DISP_GAIN decimal(25,0) 
AE_EFFECT_OP decimal(25,0) 
OTH_GAIN decimal(25,0) 
OPERATE_PROFIT decimal(25,0) 
NOPERATE_INCOME decimal(25,0) 
NOPERATE_EXP decimal(25,0) 
NCA_DISPLOSS decimal(25,0) 
OTH_EFFECT_TP decimal(25,0) 
AE_EFFECT_TP decimal(25,0) 
T_PROFIT decimal(25,0) 
INCOME_TAX decimal(25,0) 
OTH_EFFECT_NP decimal(25,0) 
AE_EFFECT_NP decimal(25,0) 
N_INCOME decimal(25,0) 
GOING_CONCERN_NI decimal(25,0) 
QUIT_CONCERN_NI decimal(25,0) 
N_INCOME_ATTR_P decimal(25,0) 
N_INCOME_BMA decimal(25,0) 
MINORITY_GAIN decimal(25,0) 
OTH_EFFECT_NPP decimal(25,0) 
AE_EFFECT_NPP decimal(25,0) 
BASIC_EPS decimal(25,0) 
DILUTED_EPS decimal(25,0) 
OTH_COMPR_INCOME decimal(25,0) 
OTH_EFFECT_CI decimal(25,0) 
AE_EFFECT_CI decimal(25,0) 
T_COMPR_INCOME decimal(25,0) 
COMPR_INC_ATTR_P decimal(25,0) 
COMPR_INC_ATTR_M_S decimal(25,0) 
OTH_EFFECT_PCI decimal(25,0) 
AE_EFFECT_PCI

抱歉，我实在不知道题目中的General Bussiness是这个表哪一列
在这里插入图片描述

select    Merchant_id,sum(substring_index(Discount_rate,':',-1) ) as manjian from ccf_offline_stage1_train
where Discount_rate is  not null and Date is not null
group by  Merchant_id
order by manjian desc
limit 3;

这一题没有交集
取得的最大值是两个单独的表里面最大的三个
在这里插入图片描述

select p1.Merchant_id,p2.Merchant_id,p1.manjian+p2.manjian as sum from
(select    Merchant_id,sum(substring_index(Discount_rate,':',-1) ) as manjian from ccf_offline_stage1_train
where Discount_rate is  not null and Date is not null
group by  Merchant_id ) as p1
,
(select    Merchant_id,sum(substring_index(Discount_rate,':',-1) ) as manjian from ccf_online_stage1_train
where Discount_rate is  not null and Date is not null
group by  Merchant_id) as p2
where p1.Merchant_id = p2.Merchant_id
order by sum desc
;