SQL训练营-Task06-综合练习题-10道经典题目-天池龙珠计划SQL训练营-20240505

已于 2024-05-06 22:27:42 修改
阅读量11 收藏
点赞数
分类专栏： 数据库与SQL 文章标签： sql 数据库
于 2024-05-05 20:18:38 首次发布
原文链接：https://tianchi.aliyun.com/forum/post/170359
版权

文章目录


如果你还没有学习过SQL训练营的前几个章节知识， 

Task 1 SQL基础：初识数据库与SQL-安装与基本介绍等
Task 2 SQL基础：查询与排序-select、运算符、聚合分组查询等
Task 3 SQL进阶：复杂查询方法-视图、子查询、函数等
Task 4 SQL进阶： 集合运算-表的加减法和join等
Task 5 SQL进阶：SQL高级处理-窗口函数等

可以点击SQL训练营页面地址进入学习：https://tianchi.aliyun.com/specials/promotion/aicampsql
天池龙珠计划训练营地址：https://tianchi.aliyun.com/specials/promotion/aicamps

下载相关创建表格和插入数据脚本

这里为了方便大家，数据脚本已经给大家准备好啦。
点击下方链接直接下载
创建数据表脚本：http://tianchi-media.oss-cn-beijing.aliyuncs.com/dragonball/SQL/create_table.sql
插入数据脚本：https://tianchi-media.oss-cn-beijing.aliyuncs.com/dragonball/SQL/data.zip

大家下载好脚本后，先在MySQL环境中运行create_table.sql脚本，创建数据表，然后解压下载好的data.zip，解压后目录如下：

8-10ccf_offline_stage1_train.sql
6-winequality-white.sql
5-8-10ccf_online_stage1_train.sql
4-macro industry.sql
3-ccf_offline_stage1_test_revised.sql
2-winequality-red.sql
1-9income statement.sql
1-9company operating.sql
1-7market data.sql

脚本文件名前面的序号表示用到该数据集的题目序号，例如1-7market data.sql表示第1题和第7题用到了该数据集。

同样的，这里给大家的也是sql脚本，里面是插入数据的语句，大家只需打开后在MySQL环境中运行即可将数据导入到数据表中。

练习题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 StatementTICKER_SYMBOL
Income StatementEND_DATE
Income StatementT_REVENUE
Income StatementT_COGS
Income StatementN_INCOME
Market DataTICKER_SYMBOL
Market DataEND_DATE_
Market DataCLOSE_PRICE
Company OperatingTICKER_SYMBOL
Company OperatingINDIC_NAME_EN
Company OperatingEND_DATE
Company OperatingVALUE

将数据表导⼊数据库，根据表结构和字段含义，Company Operating表使⽤sheet-EN(使⽤CN也可以)，Income Statement表使⽤sheet-General Business，Market Data使⽤sheet-Data。

SELECT MarketData.*,
    OperatingData.INDIC_NAME_EN,
    OperatingData.VALUE,
    IncomeStatement.N_INCOME,
    IncomeStatement.T_COGS,
    IncomeStatement.T_REVENUE 
FROM ( 
    SELECT TICKER_SYMBOL,
        END_DATE,
        CLOSE_PRICE
    FROM `market data` 
    WHERE TICKER_SYMBOL IN ('600383','600048')
) MarketData
LEFT JOIN -- operating data
    (SELECT TICKER_SYMBOL,
        INDIC_NAME_EN,
        END_DATE,
        VALUE 
    FROM `company operating`
    WHERE TICKER_SYMBOL IN ('600383','600048')
    ) OperatingData
ON MarketData.TICKER_SYMBOL = OperatingData.TICKER_SYMBOL
    AND MarketData.END_DATE = OperatingData.END_DATE
LEFT JOIN -- income statement
    (SELECT DISTINCT TICKER_SYMBOL,
        END_DATE,
        T_REVENUE,
        T_COGS,
        N_INCOME
    FROM `income statement`
    WHERE TICKER_SYMBOL IN ('600383','600048')
    ) IncomeStatement
ON MarketData.TICKER_SYMBOL = IncomeStatement.TICKER_SYMBOL
    AND MarketData.END_DATE = IncomeStatement.END_DATE
ORDER BY MarketData.TICKER_SYMBOL, MarketData.END_DATE

练习题2：

数据来源：https://tianchi.aliyun.com/dataset/dataDetail?dataId=44

请使用 Wine Quality Data 数据集《winequality-red.csv》，找出 pH=3.03的所有红葡萄酒，然后，对其 citric acid 进行中式排名（相同排名的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”）

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

练习题3：

数据来源：https://tianchi.aliyun.com/competition/entrance/231593/information

使用Coupon Usage Data for O2O中的数据集《ccf_offline_stage1_test_revised.csv》，试分别找出在2016年7月期间，发放优惠券总金额最多和发放优惠券张数最多的商家。

这里只考虑满减的金额，不考虑打几折的优惠券。

-- 发放优惠券总⾦额最多的商家
SELECT Merchant_id,
       -- SUM(SUBSTRING_INDEX(`Discount_rate`,':', 1)) AS sale_amount,
       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：

数据来源：https://tianchi.aliyun.com/dataset/dataDetail?dataId=1074

请使用A股上市公司季度营收预测中的数据集《Macro&Industry.xlsx》中的sheet-INDIC_DATA，请计算全社会用电量:第一产业:当月值在2015年用电最高峰是发生在哪月？并且相比去年同期增长/减少了多少个百分比？

-- 2015年⽤电最⾼峰是发⽣在哪⽉
SELECT PERIOD_DATE,
       MAX(DATA_VALUE) AS 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 AS YoY
FROM (
    SELECT PERIOD_DATE,
           MAX(DATA_VALUE) AS 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) AS 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：

数据来源：https://tianchi.aliyun.com/competition/entrance/231593/information

使用Coupon Usage Data for O2O中的数据集《ccf_online_stage1_train.csv》，试统计在2016年6月期间，线上总体优惠券弃用率为多少？并找出优惠券弃用率最高的商家。

弃用率 = 被领券但未使用的优惠券张数 / 总的被领取优惠券张数

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

练习题6：

数据来源：https://tianchi.aliyun.com/dataset/dataDetail?dataId=44

请使用 Wine Quality Data 数据集《winequality-white.csv》，找出 pH=3.63的所有白葡萄酒，然后，对其 residual sugar 量进行英式排名（非连续的排名）

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

练习题7：

数据来源：https://tianchi.aliyun.com/dataset/dataDetail?dataId=1074

请使用A股上市公司季度营收预测中的数据集《Market Data.xlsx》中的sheet-DATA，

计算截止到2018年底，市值最大的三个行业是哪些？以及这三个行业里市值最大的三个公司是哪些？（每个行业找出前三大的公司，即一共要找出9个）

-- 计算截⽌到2018年底，市值最⼤的三个⾏业是哪些？
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;

-- 这三个⾏业⾥市值最⼤的三个公司是哪些？
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) AS 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：

数据来源：https://tianchi.aliyun.com/competition/entrance/231593/information

使用Coupon Usage Data for O2O中的数据集《ccf_online_stage1_train.csv》和《ccf_offline_stage1_train.csv》，试找出在2016年6月期间，线上线下累计优惠券使用次数最多的顾客。

SELECT User_id,
       SUM(couponCount) AS couponCount
FROM (
    SELECT User_id,
           COUNT(*) AS 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(*) AS 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：

数据来源：https://tianchi.aliyun.com/dataset/dataDetail?dataId=1074

请使用A股上市公司季度营收预测数据集《Income Statement.xls》中的sheet-General Business和《Company Operating.xlsx》中的sheet-EN。

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

-- 因为正好是第⼀季度，所以不需要减。 如果是2季度，单季度净利润需要⽤2季度的值减去1⽉份的
SELECT *
FROM (
    SELECT TICKER_SYMBOL,
           YEAR(END_DATE) AS Year,
           QUARTER(END_DATE) AS QUARTER,
           SUM(VALUE) AS 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) AS Year,
           QUARTER(END_DATE) AS QUARTER,
           SUM(N_INCOME) AS Amount
    FROM `income statement`
    GROUP BY TICKER_SYMBOL, YEAR(END_DATE), QUARTER(END_DATE)
) Income
ON BaseData.TICKER_SYMBOL = Income.TICKER_SYMBOL
AND BaseData.Year = Income.Year
AND BaseData.QUARTER = Income.QUARTER;

练习题10：

数据来源：https://tianchi.aliyun.com/competition/entrance/231593/information

使用Coupon Usage Data for O2O中的数据集《ccf_online_stage1_train.csv》和《ccf_offline_stage1_train.csv》，试找出在2016年6月期间，线上线下累计被使用优惠券满减最多的前3名商家。

比如商家A，消费者A在其中使用了一张200减50的，消费者B使用了一张30减1的，那么商家A累计被使用优惠券满减51元。

SELECT Merchant_id,
       SUM(discount_amount) AS 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;

参考来源

  1. Task06：综合练习题-10道经典题目-天池龙珠计划SQL训练营
Yahooo-
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL2022-SSEI-Dev
11-14
SQL2022-SSEI-Dev
SQL：SQL-92，SQL-99和SQL-2003的BNF语法
01-30
SQL：SQL-92，SQL-99和SQL-2003的BNF语法
sql-ddl-to-json-schema:SQL DDL到JSON模式转换器
05-17
SQL DDL至JSON模式转换器 将SQL DDL语句转换为JSON格式（JSON模式和紧凑格式）。 承诺中 了解内部 手边的脚本Visual Studio程式码 链接 概述 使用以下SQL： CREATE TABLE users ( id INT ( 11 ) NOT NULL AUTO_...
Elasticsearch JDBC driver（x-pack-sql-jdbc-8.5.3.jar）
01-24
Elasticsearch JDBC driver（x-pack-sql-jdbc-8.5.3.jar）
spark-sql_2.11-2.4.0-cdh6.1.1.jar
05-14
spark-sql_2.11-2.4.0-cdh6.1.1.jar
SQLAlchemy 连接池
youhebuke225的博客
06-17 211
调整这些参数可以帮助你更好地管理数据库连接，并减少因连接超时或服务器断开连接而导致的问题。记得根据你的应用程序的需求和数据库服务器的配置来合理设置这些参数。在 SQLAlchemy 中，确实可以通过配置连接池的参数来管理连接的超时和回收，从而确保连接在需要时能够被重新建立。SQLAlchemy 使用的是。作为其默认的连接池实现。为了配置这些参数，你可以在创建引擎时将它们传递给。
PostgreSQL源码分析——CREATE TYPE
让我思考一下
06-18 311
定义一个复合类型某种程度上就是定义一张新的表，表的rowtype就是复合类型。会看到创建一个复合类型的同时会创建一个同名的表。这个表可以在pg_class系统表中查到，但是通过\d等却查不到，也不能想表中插入数据，因为它并不是真正意义上的表，只是用表实现复合类型。这部分因为不是可优化语句，没有什么处理。这部分因为是Utility语句，没有什么处理的。创建复合类型，我们分析一下其源码实现。
SQL中distinct去重关键字的使用和count统计组合的使用
睡不醒的每天
06-13 521
distinct必须在列的前面，否则直接提示错误，当distinct作用于单列的的情况下，统计查询的结果为行数目的总数，包含null为空的数目。count(distinct 列名)：根据指定的列统计记录总数，不包含重复的记录，不包括NULL或空的值。select distinct 列名1，列名2，列名3…select distinct 列名 from 表；也可以使用count(2)、count(3)等等。count(distinct 列名)
如何减少sql出现问题
smile6868的博客
06-20 229
在编写 SQL 时遇到小问题是很常见的，尤其是当你对 SQL 语言、数据库设计或业务需求不够熟悉时。
PostgreSQL源码分析——CREATE CAST
让我思考一下
06-18 295
语句自定义一个类型转换，数据库肯定有个地方将这个转换的信息存起来，这个地方就是pg_cast系统表。pg_cast系统表存在数据类型转换路径，包括内建和用户自定义的。函数的主要内容就是将用户自定义的类型转换信息插入到pg_cast系统表中。下面我们主要分析一下其源码，看一下是如何实现的。我们首先分析一下其语法解析部分，这部分比较简单，核心是。如何使用以及用法请参考PostgreSQL文档中。后续会重点分析一下这个函数。—— 定义一个用户自定义的类型转换。非常容易理解，下面我们重点分析一下。
PostgreSQL源码分析 —— CAST类型转换
让我思考一下
06-18 442
我们分析一下PostgreSQL中显式类型转换是如何实现的，在PostgreSQL中，显式类型转换可以用等同于。这里我们以为例进行分析。再结合之前对的分析，基本就弄清了一部分PostgreSQL中数据类型转换问题。当然要完整的理解类型转换还需要完整的理解PostgreSQL中的类型系统，后续还需要我们逐步的深入理解。
PostgreSQL源码分析——视图查询重写
让我思考一下
06-18 472
这里我们分析一下查询重写的过程，主要分析视图的查询重写的过程。
【LLM之NL2SQL】DAIL-SQL论文阅读笔记
<p>专注挖坑</p>
06-20 450
该研究旨在提供一个全面、系统的评估框架，用于评估基于大型语言模型（LLM）的Text-to-SQL技术。特别强调了不同的提示工程策略的有效性和效率，以及开源LLM的可行性。研究的重点是评估在零样本和少样本场景下的不同问题表示方式，样本选择和组织策略的效果。
PostgreSQL的系统视图pg_policies
lee_vincent1的博客
06-14 451
是 PostgreSQL 的一个系统视图，用于显示数据库中定义的行级安全策略（Row-Level Security Policies）。行级安全策略是一种访问控制机制，它允许你定义基于行的访问控制规则，通过策略来控制用户对特定行数据的访问。行级安全策略在 PostgreSQL 9.5 及之后的版本中引入，旨在增强数据库的安全性，使其能够支持更细粒度的访问控制。
数据库-数据定义和操纵-DDL语言的使用
weixin_72982195的博客
06-17 533
唯一约束（Unique Key）是指所有记录中字段的值不能重复出现。唯一约束与主键约束相似的是它们都可以确保列的唯一性。唯一约束在一个表中可有多个，并且设置唯一约束的列允许有空值，但是只能有一个空值。而主键约束在一个表中只能有一个，且不允许有空值。
SQL - CTE
最新发布
全村的希望的博客
06-20 212
CTE 是一个非常有用的 SQL 工具，特别是在处理复杂查询时。它通过分解查询、提高可读性和支持递归查询，显著增强了 SQL 的功能和灵活性selectid,tag_name,trace_id,id1,tag_name1,remarkfromdlaorder byid asclimit1000offset0selectid,tag_name,trace_id,id1,tag_name1,remarkfromdlaorder byid asclimit。
PostgreSQL源码分析——CREATE TABLE（series）
让我思考一下
06-18 217
这里分析一下建表时含有序列的时候PostgreSQL是如何创建的，比如下面的SQL语句。
liquibase 错误: 无法打开扩展控制文件 “c:/postgresql/14/share/extension/timescaledb.control“
weixin_52047574的博客
06-17 961
英语能力还是重要啊，无论是文档还是github上的解决方案都是英文的，我一看到英文就有抵触情绪，但是这次为了解决这个问题，硬着头皮也要看下去，不过现在好多了，可以找这些大模型帮忙翻译。
PostgreSQL源码分析——CREATE EXTENSION
让我思考一下
06-18 357
本文分析一下CREATE EXTENSION的源码实现。顺带学习一下PG中的插件相关的原理。
mysql sql练习题_Mysql----sql语句练习题(一)
06-11
好的，我可以为您提供一些MySQL SQL语句练习题。以下是一些简单的练习题： 1. 创建一个名为"students"的表，包含以下列：id, name, age, gender。 2. 向表"students"中插入以下数据： | id | name | age | gender...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值