SQL173 店铺901国庆期间的7日动销率和滞销率

SQL题解：店铺动销率与滞销率计算

关键：只要当天任一店铺有任何商品的销量就输出该天的结果，即使店铺901当天的动销率为0。

潜台词：​输出逻辑与店铺901的销售情况无关，只取决于平台整体是否有销售记录。​

  • ​表面理解​：计算店铺901的动销率 → 应该只关注901的数据
  • ​实际规则​：只要全平台当天有任何销售（哪怕不是901的），就必须输出901这天的结果
条件是否输出
全平台当天有任意销售✅ 必须输出（即使901销售为0）
全平台当天无任何销售❌ 不输出

题目理解

题目要求计算店铺901在2021年国庆头3天（10月1日-10月3日）的7日动销率和滞销率，结果保留3位小数，按日期升序排序。

关键定义

  • ​动销率​：一段时间内有销量的商品占当前已上架总商品数的比例
  • ​滞销率​：一段时间内没有销量的商品占当前已上架总商品数的比例

特殊要求

"只要当天任一店铺有任何商品的销量就输出该天的结果，即使店铺901当天的动销率为0"

解题思路分析

1. 数据准备

首先需要从三个表中获取必要信息：

  • tb_order_overall：订单基本信息
  • tb_order_detail：订单商品明细
  • tb_product_info：商品信息（店铺ID在这里）

2. 核心逻辑

  1. 确定需要计算的日期范围（10月1日-10月3日）
  2. 计算7日滚动窗口内的动销商品数
  3. 计算当前已上架的商品总数
  4. 计算动销率和滞销率

SQL代码解析

CTE部分

date_range表：获取平台有销售记录的日期
  date_range AS (SELECT DISTINCT DATE(event_time) AS order_dateFROM tb_order_overallWHERE DATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03')
  • 从订单总表中筛选出国庆3天内有销售记录的日期
  • 使用distinct确保日期不重复
shop_products 表：获取店铺901的销售记录
shop_products AS (SELECTDATE(a.event_time) AS order_date,b.product_idFROMtb_order_overall aJOIN tb_order_detail b ON a.order_id = b.order_id AND a.status = 1JOIN tb_product_info c ON b.product_id = c.product_id AND c.shop_id = '901')
  • 关联三个表获取店铺901的有效订单（status=1）的商品记录
  • 结果包含日期和商品ID
product_counts 表：计算每日在售商品数
product_counts AS (SELECTDATE(o.event_time) AS order_date,COUNT(DISTINCT p.product_id) AS total_products
FROMtb_order_overall oCROSS JOIN tb_product_info p -- 显式交叉连接
WHEREp.shop_id = '901'AND DATEDIFF(DATE(o.event_time), DATE(p.release_time)) >= 0
GROUP BYDATE(o.event_time)
)
  • 计算店铺901在每个订单日期时已经上架的商品总数
  • DATEDIFF(...) >= 0：只保留商品上架时间早于或等于订单日期的记录
  • 使用逗号,表示的是隐式交叉连接​（CROSS JOIN），这会生成两个表的笛卡尔积

商品表​（p）：

product_id | shop_id | release_time
----------------------------
8001      | 901     | 2020-01-01
8002      | 901     | 2020-01-01
8003      | 901     | 2021-09-01

​订单表​（o）：

order_id | event_time
---------------------
301004   | 2021-10-01
301005   | 2021-10-02
301003   | 2021-10-03

连接后会产生 3商品 × 3订单 = 9行中间结果：

product_id | shop_id | release_time | order_id | event_time
-----------------------------------------------------------
8001       | 901     | 2020-01-01   | 301004   | 2021-10-01
8001       | 901     | 2020-01-01   | 301005   | 2021-10-02
8001       | 901     | 2020-01-01   | 301003   | 2021-10-03
8002       | 901     | 2020-01-01   | 301004   | 2021-10-01
8002       | 901     | 2020-01-01   | 301005   | 2021-10-02
8002       | 901     | 2020-01-01   | 301003   | 2021-10-03
8003       | 901     | 2021-09-01   | 301004   | 2021-10-01
8003       | 901     | 2021-09-01   | 301005   | 2021-10-02
8003       | 901     | 2021-09-01   | 301003   | 2021-10-03

按订单日期分组后，计算每个日期的唯一商品数：

order_date | COUNT(DISTINCT p.product_id)
---------------------------
2021-10-01 | 3 (8001,8002,8003都已上架)
2021-10-02 | 3
2021-10-03 | 3

主查询部分

SELECTdr.order_date AS dt,ROUND(COUNT(DISTINCT sp.product_id) / AVG(pc.total_products),3) AS sale_rate,ROUND(1 - COUNT(DISTINCT sp.product_id) / AVG(pc.total_products),3) AS unsale_rate
FROMdate_range drLEFT JOIN shop_products sp ON DATEDIFF(dr.order_date, sp.order_date) BETWEEN 0 AND 6JOIN product_counts pc ON dr.order_date = pc.order_date
GROUP BYdr.order_date
ORDER BYdr.order_date
  • 计算动销率和滞销率
  • ​date_range dr​：国庆3天的日期（10月1日、2日、3日）
  • ​LEFT JOIN shop_products​：关联7天内的销售记录
    • DATEDIFF(dr.order_date, sp.order_date) BETWEEN 0 AND 6表示：
      • 0：当天
      • 6：7天前（含当天共7天）
  • ​JOIN product_counts​：关联每天的商品总数
  • 分子​：COUNT(DISTINCT sp.product_id)
    • 计算7天内销售过的不同商品数量
  • ​分母​：AVG(pc.total_products)
    • 因为pc已经按日期分组，每个日期只有一行，AVG相当于直接取值
    • 表示当天已上架的商品总数
  • ​ROUND(..., 3)​​：保留3位小数

关键理解要点

  1. ​时间窗口​：每个日期的前7天（含当天）
  2. ​三层数据组合​：
    • 基础日期 + 7天内销售记录 + 当日商品总数
  3. ​LEFT JOIN的意义​：确保无销售日也能显示
  4. ​AVG的使用场景​：对单值分组列的巧妙处理

    自建测试数据

    USE niuke_mall;-- (1)创建商品信息表
CREATE TABLE tb_product_info (id INT AUTO_INCREMENT PRIMARY KEY,product_id INT NOT NULL,shop_id INT NOT NULL,tag VARCHAR(20),in_price DECIMAL(10,2) NOT NULL,quantity INT NOT NULL,release_time DATETIME NOT NULL
);-- 插入测试数据
INSERT INTO tb_product_info (product_id, shop_id, tag, in_price, quantity, release_time) VALUES
-- 901店铺的商品(3个)
(8001, 901, '日用', 60.00, 1000, '2020-01-01 10:00:00'),  -- 长期在售商品
(8002, 901, '零食', 140.00, 500, '2020-01-01 10:00:00'), -- 长期在售商品
(8003, 901, '零食', 160.00, 500, '2021-09-01 10:00:00'), -- 较晚上架商品-- 902店铺的商品(3个)
(8004, 902, '电子', 1200.00, 200, '2021-08-15 10:00:00'), -- 高单价商品
(8005, 902, '电子', 800.00, 150, '2021-09-15 10:00:00'),  -- 中等单价商品
(8006, 902, '配件', 50.00, 1000, '2021-10-01 10:00:00'),  -- 新上架商品-- 903店铺的商品(4个)
(8007, 903, '服装', 200.00, 300, '2021-07-01 10:00:00'),  -- 夏季服装
(8008, 903, '服装', 300.00, 250, '2021-09-01 10:00:00'),  -- 秋季服装
(8009, 903, '鞋帽', 400.00, 200, '2021-09-15 10:00:00'),  -- 鞋类商品
(8010, 903, '鞋帽', 350.00, 180, '2021-10-01 10:00:00');  -- 新上架鞋类-- (2)创建订单总表
CREATE TABLE tb_order_overall (id INT AUTO_INCREMENT PRIMARY KEY,order_id INT NOT NULL,uid INT NOT NULL,event_time DATETIME NOT NULL,total_amount DECIMAL(10,2) NOT NULL,total_cnt INT NOT NULL,`status` TINYINT NOT NULL DEFAULT 1 COMMENT '1-已完成'
);-- 插入测试数据
INSERT INTO tb_order_overall (order_id, uid, event_time, total_amount, total_cnt, status) VALUES
-- 国庆前订单(3个)
(301001, 101, '2021-09-28 10:00:00', 300.00, 2, 1),  -- 901店铺订单
(301002, 102, '2021-09-29 11:00:00', 450.00, 3, 1),  -- 902店铺订单
(301003, 103, '2021-09-30 14:00:00', 200.00, 1, 1),  -- 903店铺订单-- 国庆期间订单(9个)
-- 10月1日
(301004, 101, '2021-10-01 10:00:00', 170.00, 1, 1),  -- 901店铺订单
(301005, 102, '2021-10-01 11:00:00', 800.00, 1, 1),  -- 902店铺订单
(301006, 103, '2021-10-01 14:00:00', 350.00, 1, 1),  -- 903店铺订单-- 10月2日
(301007, 104, '2021-10-02 09:00:00', 300.00, 2, 1),  -- 901店铺订单
(301008, 105, '2021-10-02 10:30:00', 1200.00, 1, 1), -- 902店铺订单
(301009, 106, '2021-10-02 15:00:00', 600.00, 2, 1),  -- 903店铺订单-- 10月3日
(301010, 107, '2021-10-03 10:00:00', 235.00, 2, 1),  -- 901店铺订单
(301011, 108, '2021-10-03 11:30:00', 1600.00, 2, 1), -- 902店铺订单
(301012, 109, '2021-10-03 16:00:00', 950.00, 3, 1);  -- 903店铺订单-- (3)创建订单明细表
CREATE TABLE tb_order_detail (id INT AUTO_INCREMENT PRIMARY KEY,order_id INT NOT NULL,product_id INT NOT NULL,price DECIMAL(10,2) NOT NULL,cnt INT NOT NULL
);-- 插入测试数据
INSERT INTO tb_order_detail (order_id, product_id, price, cnt) VALUES
-- 国庆前订单明细
(301001, 8002, 150.00, 2),  -- 901店铺商品
(301002, 8004, 400.00, 1),  -- 902店铺商品
(301002, 8005, 800.00, 1),  -- 902店铺商品
(301002, 8006, 50.00, 1),   -- 902店铺商品
(301003, 8007, 200.00, 1),  -- 903店铺商品-- 国庆期间订单明细
-- 10月1日
(301004, 8002, 170.00, 1),  -- 901店铺商品
(301005, 8005, 800.00, 1),  -- 902店铺商品
(301006, 8010, 350.00, 1),  -- 903店铺商品-- 10月2日
(301007, 8002, 150.00, 1),  -- 901店铺商品
(301007, 8003, 150.00, 1),  -- 901店铺商品
(301008, 8004, 1200.00, 1), -- 902店铺商品
(301009, 8008, 300.00, 2),  -- 903店铺商品-- 10月3日
(301010, 8001, 85.00, 1),   -- 901店铺商品
(301010, 8003, 150.00, 1),  -- 901店铺商品
(301011, 8004, 1200.00, 1), -- 902店铺商品
(301011, 8005, 400.00, 1),  -- 902店铺商品
(301012, 8007, 200.00, 1),  -- 903店铺商品
(301012, 8008, 300.00, 1),  -- 903店铺商品
(301012, 8009, 450.00, 1);  -- 903店铺商品

    参考大佬们的回答

    题解 | #店铺901国庆期间的7日动销率和滞销率#_牛客博客

    题解 | #店铺901国庆期间的7日动销率和滞销率#_牛客博客

     -- 定义日期范围CTE，获取2021-10-01到2021-10-03期间的所有订单日期
WITH-- 步骤1：确定要计算的日期范围（国庆3天）date_range AS (SELECT DISTINCTDATE(event_time) AS order_dateFROMtb_order_overallWHEREDATE(event_time) BETWEEN '2021-10-01' AND '2021-10-03'),-- 步骤2：找出901店铺有销售的商品和销售日期shop_products AS (SELECTDATE(a.event_time) AS order_date,b.product_idFROMtb_order_overall aJOIN tb_order_detail b ON a.order_id = b.order_idAND a.status = 1JOIN tb_product_info c ON b.product_id = c.product_idAND c.shop_id = '901'),-- 定义产品总数CTE，计算901店铺每天可销售的产品总数product_counts as (SELECTDATE(o.event_time) AS order_date,COUNT(DISTINCT p.product_id) AS total_productsFROMtb_order_overall oCROSS JOIN tb_product_info p -- 显式交叉连接WHEREp.shop_id = '901'AND DATEDIFF(DATE(o.event_time), DATE(p.release_time)) >= 0GROUP BYDATE(o.event_time))-- 主查询：计算每天的产品销售率和未销售率-- 最终计算
SELECTdr.order_date AS dt,ROUND(COUNT(DISTINCT sp.product_id) / AVG(pc.total_products),3) AS sale_rate,ROUND(1 - COUNT(DISTINCT sp.product_id) / AVG(pc.total_products),3) AS unsale_rate
FROMdate_range drLEFT JOIN shop_products sp ON DATEDIFF(dr.order_date, sp.order_date) BETWEEN 0 AND 6JOIN product_counts pc ON dr.order_date = pc.order_date
GROUP BYdr.order_date
ORDER BYdr.order_date

    本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
    如若转载，请注明出处：http://www.pswp.cn/web/90886.shtml
    繁体地址，请注明出处：http://hk.pswp.cn/web/90886.shtml
    英文地址，请注明出处：http://en.pswp.cn/web/90886.shtml

    如若内容造成侵权/违法违规/事实不符，请联系英文站点网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

    相关文章

    PytorchLightning最佳实践基础篇

    PytorchLightning最佳实践基础篇

    PyTorch Lightning（简称 PL）是一个建立在 PyTorch 之上的高层框架，核心目标是剥离工程代码与研究逻辑，让研究者专注于模型设计和实验思路，而非训练循环、分布式配置、日志管理等重复性工程工作。本文从基础到进阶&…
    阅读更多...
    Apache Flink 实时流处理性能优化实践指南

    Apache Flink 实时流处理性能优化实践指南

    Apache Flink 实时流处理性能优化实践指南 随着大数据和实时计算需求不断增长，Apache Flink 已经成为主流的流处理引擎。然而，在生产环境中，高并发、大吞吐量和低延迟的业务场景对 Flink 作业的性能提出了更高要求。本文将从原理层面深入解析…
    阅读更多...
    ubuntu上将TempMonitor加入开机自动运行的方法

    ubuntu上将TempMonitor加入开机自动运行的方法

    1.新建一个TempMonitor.sh文件，内容如下：#!/bin/bashcd /fjrobot/ ./TempMonitor &2.执行以下命令chmod x TempMonitor chmod x TempMonitor.sh rm -rf /etc/rc2.d/S56TempMonitor rm -rf /etc/init.d/TempMonitor cp /fjrobot/TempMonitor.sh /etc/…
    阅读更多...
    速卖通自养号测评技术解析：IP、浏览器与风控规避的实战方案

    速卖通自养号测评技术解析：IP、浏览器与风控规避的实战方案

    一、速卖通的“春天”来了，卖家如何抓住机会？2025年的夏天，速卖通的风头正劲。从沙特市场跃升为第二大电商平台，到8月大促返佣力度升级，平台对优质商家的扶持政策越来越清晰。但与此同时，竞争也愈发激烈——…
    阅读更多...
    adb: CreateProcessW failed: 系统找不到指定的文件

    adb: CreateProcessW failed: 系统找不到指定的文件

    具体错误 adb devices * daemon not running; starting now at tcp:5037 adb: CreateProcessW failed: 系统找不到指定的文件。 (2) * failed to start daemon adb.exe: failed to check server version: cannot connect to daemon 下载最新的platform-tools-windows 下载最新…
    阅读更多...
    Centos安装HAProxy搭建Mysql高可用集群负载均衡

    Centos安装HAProxy搭建Mysql高可用集群负载均衡

    接上文MYSQL高可用集群搭建–docker https://blog.csdn.net/weixin_43914685/article/details/149647589?spm1001.2014.3001.5501 连接到你搭建的 Percona XtraDB Cluster (PXC) 数据库集群，实现高可用性和负载均衡，建议使用一个中间件来管理这些连接。…
    阅读更多...
    Sql server开挂的OPENJSON

    Sql server开挂的OPENJSON

    以前一直用sql server2008，自从升级成sql server2019后，用OPENJSON的感觉像开挂，想想以前表作为参数传输时的痛苦，不堪回首。一》不堪回首 为了执行效率，很多时候希望将表作为参数传给数据库的存储过程。存储过程支持自…
    阅读更多...
    【数据结构】队列和栈练习

    【数据结构】队列和栈练习

    1.用队列实现栈 225. 用队列实现栈 - 力扣（LeetCode） typedef int QDatatype; typedef struct QueueNode {struct QueueNode *next;QDatatype data; }QNode;typedef struct Queue {QNode* head;QNode* tail;QDatatype size; }Que;typedef struct {Que…
    阅读更多...
    LabVIEW二维码实时识别

    LabVIEW二维码实时识别

    ​LabVIEW通过机器视觉技术，集成适配硬件构建二维码实时识别系统。通过图像采集、预处理、定位及识别全流程自动化，解决复杂环境下二维码识别效率低、准确率不足问题，满足工业产线追溯、物流分拣等实时识别需求。应用场景适用于工业产线追溯&…
    阅读更多...
    微服务-springcloud-springboot-Skywalking详解（下载安装）

    微服务-springcloud-springboot-Skywalking详解（下载安装）

    一、SkyWalking核心介绍 1. 什么是SkyWalking？ Apache SkyWalking是一款国人主导开发的开源APM（应用性能管理）系统，2015年由吴晟创建，2017年进入Apache孵化器，2019年毕业成为Apache顶级项目。它通过分布式…
    阅读更多...
    Elasticsearch 字段值过长导致索引报错问题排查与解决经验总结

    Elasticsearch 字段值过长导致索引报错问题排查与解决经验总结

    在最近使用 Elasticsearch 的过程中，我遇到了一个 字段值过长导致索引失败 的问题。经过排查和多次尝试，最终通过设置字段 "index": false 方式解决。本文将从问题现象、排查过程、问题分析、解决方案和建议等方面，详细记录这次踩坑…
    阅读更多...
    使用idea 将一个git分支的部分记录合并到git另一个分支

    使用idea 将一个git分支的部分记录合并到git另一个分支

    场景： 有多个版本分支，需要将其中一个分支的某一两次提交合并到指定分支上 eg： 将v1.0.0分支中指定提交记录 合并到 v1.0.1分支中 操作： 步骤一 idea切换项目分支到v1.0.1(需要合并到哪个分支就先站到哪个分支上) 步骤二 在ide…
    阅读更多...
    基于深度学习的图像分类：使用ShuffleNet实现高效分类

    基于深度学习的图像分类：使用ShuffleNet实现高效分类

    前言 图像分类是计算机视觉领域中的一个基础任务，其目标是将输入的图像分配到预定义的类别中。近年来，深度学习技术，尤其是卷积神经网络（CNN），在图像分类任务中取得了显著的进展。ShuffleNet是一种轻量级的…
    阅读更多...
    OpenGL里相机的运动控制

    OpenGL里相机的运动控制

    相机的核心构造一个是glm::lookAt函数，一个是glm::perspective函数，本文相机的一切运动都在于如何构建相应的参数传入上述两个函数里。glm::mat4 glm::lookAt(glm::vec3 const &eye,//相机所在位置glm::vec3 const &center,//要凝视的点glm::vec…
    阅读更多...
    java设计模式 -【策略模式】

    java设计模式 -【策略模式】

    策略模式定义 策略模式（Strategy Pattern）是一种行为设计模式，允许在运行时选择算法的行为。它将算法封装成独立的类，使得它们可以相互替换，而不影响客户端代码。 核心组成 Context（上下文）&…
    阅读更多...
    项目重新发布更新缓存问题，Nginx清除缓存更新网页

    项目重新发布更新缓存问题，Nginx清除缓存更新网页

    server {listen 80;server_name your.domain.com; # 替换为你的域名root /usr/share/nginx/html; # 替换为你的项目根目录# 规则1：HTML 文件 - 永不缓存# 这是最关键的一步，确保浏览器总是获取最新的入口文件。location /index.html {add_header Cache-…
    阅读更多...
    系统架构师：系统安全与分析-思维导图

    系统架构师：系统安全与分析-思维导图

    系统安全与分析的定义​​系统安全与分析是系统架构师在系统全生命周期中贯穿的核心职责，其本质是通过​​识别、评估、防控安全风险，并基于数据与威胁情报进行动态分析​​，构建从技术到管理的多层次防护体系，确保系统的保密性&a…
    阅读更多...
    利用 Google Guava 的令牌桶限流实现数据处理限流控制

    利用 Google Guava 的令牌桶限流实现数据处理限流控制

    目录 一、令牌桶限流机制原理 二、场景设计与目标 三、核心实现代码（Java） 1. 完整代码实现 四、运行效果分析 五、应用建议 在高吞吐数据处理场景中，如何限制数据处理速率、保护系统资源、防止下游服务过载是系统设计中重要的环节。本文…
    阅读更多...
    小黑课堂计算机二级 WPS Office题库安装包2.52_Win中文_计算机二级考试_安装教程

    小黑课堂计算机二级 WPS Office题库安装包2.52_Win中文_计算机二级考试_安装教程

    软件下载 【名称】：小黑课堂计算机二级 WPS Office题库安装包2.52 【大小】：584M 【语言】：简体中文 【安装环境】：Win10/Win11（其他系统不清楚） 【迅雷网盘下载链接】（务必手机注册&#…
    阅读更多...
    CSS3知识补充

    CSS3知识补充

    1.伪类和伪元素： 简单的伪类实例 :first-chlid :last-child :only-child :invalid 用户行为伪类 :hover——上面提到过，只会在用户将指针挪到元素上的时候才会激活，一般就是链接元素。:focus——只会在用户使用键盘控制，选…
    阅读更多...
    最新文章

    Copyright @ 2022~2023