【SQL】Hive/Spark SQL 环比/同比日期/周期汇总

最新推荐文章于 2023-11-08 11:51:28 发布
原创 最新推荐文章于 2023-11-08 11:51:28 发布 · 置顶 · 4.2k 阅读 · 19 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#hive #sql #spark

当天

'${
   
   zdt.format("yyyy-MM-dd")}'

昨天

'${
   
   zdt.addDay(-1).format('yyyy-MM-dd')}'

前30天

'${
   
   zdt.addDay(-30).format('yyyy-MM-dd')}'

本周-上周四至本周三 “u”-星期几

between
date_sub('${
   
   zdt.format("yyyy-MM-dd")}','${
   
   zdt.format("u")}'+3)
and
date_sub('${
   
   zdt.format("yyyy-MM-dd")}','${
   
   zdt.format("u")}'-3)

上周-上上周四至上周三

between 
date_sub('${
   
   zdt.format("yyyy-MM-dd")}','${
   
   zdt.format("u")}'+10)
and 
date_sub('${
   
   zdt.format("yyyy-MM-dd")}','${
   
   zdt.format("u")}'+4)

去年同期-同期周四至同期周三

between 
date_sub('${
   
   zdt.format("yyyy-MM-dd"
最低0.47元/天 解锁文章

200万优质内容无限畅学
Spark获取json写入hive(工作实例)
someInNeed的博客
09-07 736
spark-submit \ --executor-memory 8G \ --conf spark.target.date=${dt} \ --conf spark.ui.retainedTasks=50000 \ --driver-memory 4G \ --master yarn \ --class com.cifi.zhyc.SparkImportMcYanPan\ --deploy-mode client \ {JsonSpark.jar} \ package com.cifi.zhyc .
sql案例分析：统计连续登陆、日活、蚂蚁森林、排名等
.
07-28 3877
这样处理的结果是，相同的 group by key 有可能分发到不同的 reduce 中，从而达到负载均衡的目的；问题：假设2017年1月1日开始记录低碳数据（user_low_carbon），假设2017年10月1日之前满足申领条件的用户都申领了一颗p004-胡杨，剩余的能量全部用来领取“p002-沙柳”。问题：查询user_low_carbon表中每日流水记录，条件为：用户在2017年，连续三天（或以上）的天数里，每天减少碳排放（low_carbon）都超过100g的用户低碳流水。
sparksql-分组月度环比同比自动化
weixin_49319918的博客
06-17 1187
有一个需求是按月份统计环比和同比值,每次都取一遍非常麻烦,同时也容易出错,这里我把要取的数据报表化,这里提供一个模板,这个SQL写了我好久,经过对比数据发现,这样写是没什么问题的,这里提供一些注意事项 同比环比的定义 月份同比计算 (2021-01 - 2020-01) / 2020-01 月份环比计算 (2021-02 - 2021-01) / 2021-01 在计算同比的时候要注意order by要对月份和年份都要排序,原因是lead 中的order by只指定了一个排序规则,这里都要指定,
hive SQL实现占比、同比、环比计算（lag函数，lead函数）
热门推荐
清平乐的技术专栏
10-29 2万+
前两天博文 《SQL实现占比（时间函数升级版）》， 现在用hive实现一下 SELECT month_order, year_order, number, concat( round( number / total * 100.00, 2 ), '%' ) percent FROM ( SELECT * FROM ( SELECT substr( order_time, 1,7...
hive 同比&环比&对比
nnnnn68的博客
12-16 1483
hive 同比&环比&对比
SQL提取大表的部分数据所在周/月的全部数据并根据周/月汇总，所遇到的性能问题解决
weixin_46156257的博客
10-18 341
从一个大表(TEST_TAB，大于1亿行)中根据特定条件筛选出一部分数据，在筛选这部分数据所在的月/周的数据，再根据月/周进行汇总，筛选数据的字段和日期字段(天)均有索引。这种方式可以解决按月汇总的问题，但由于周无法直接从日期字段中截取，因此无法解决按周汇总的问题。测试3：参考测试1结果，使用临时表(使用materialize hint)固话后再按周汇总。但这个SQL实际执行很慢，需要10min左右，查看了执行计划，A和B均走了相应索引。测试1：如果不进行按月汇总，SQL只需1s就执行出来了。
hive的同比、环比计算（add_months 函数）
weixin_42279393的博客
09-04 1万+
说明：同比：同去年的今天进行比较 环比：同上个月的今天进行比较 环比计算 一、先看一下数据，这里只能进行7.10号和6.10号进行对比，比到12号 这里是引用±------------------------±-----------------------±-----------------------------±-------------------------+ | bas_cgj_ssrlsj1.spotid | bas_cgj_ssrlsj1.times | bas_cgj_ssrlsj1.
【面试真题】Mysql实现计算同比、环比
Eric_data的博客
09-24 3298
真题回顾 博主的一位朋友今天去面试，题目是这样的。有一张表，里面有三个字段，分别为datetime，city，order，要求计算出2017年10月1日每个城市订单数量与上周同一天的比值。稍加分析可知，这是一道典型的求同比、环比类型的题目。 知识点梳理 同比一般情况下是今年第n月与去年第n月比。同比发展速度主要是为了消除季节变动的影响，用以说明本期发展水平与去年同期发展水平对比而达到的相对发展速...
mysql计算同比和环比的区别_【面试真题】Mysql实现计算同比、环比
weixin_36316494的博客
12-30 861
真题回顾博主的一位朋友今天去面试，题目是这样的。有一张表，里面有三个字段，分别为datetime，city，order，要求计算出2017年10月1日每个城市订单数量与上周同一天的比值。稍加分析可知，这是一道典型的求同比、环比类型的题目。 知识点梳理同比一般情况下是今年第n月与去年第n月比。同比发展速度主要是为了消除季节变动的影响，用以说明本期发展水平与去年同期发展水平对比而达到的相对发展速度。...
【SQL】SQL查询去年同期
qq_51350969的博客
11-08 4785
your_table。
【Hive SQL 每日一题】环比增长率、环比增长率、复合增长率
JIE的博客 --- moon_coder
10-22 2176
它用于比较同一时间段内的两个不同年份的数据变化情况，判断增长趋势和比较不同年度的表现。复合增长率是指在一段连续的时间内，某项指标每个月或年平均增长的复合增长率。环比增长率是指两个相邻时段之间某种指标的增长率。通常来说，环比增长率是比较两个连续时间段内某项数据的增长量大小的百分比。理解环比增长率、同比增长率、复合增长率的概念以及计算公式就能够比较容易的实现这个需求。通过计算两者之间的差异，再以百分比的形式表示出来，就得到了环比增长率。通过计算两者之间的差异，再以百分比的形式表示出来，就得到了同比增长率。
spark sql实现月销售额占比以及月环比和月同比(大厂数仓sql面试题)
weixin_33438932的博客
09-10 890
spark sql实现月销售额占比以及月环比和月同比(大厂数仓sql面试题)
HiveQL获取一年后日期计算同比及2月闰月特殊情况处理
zhangliushi的博客
08-31 2976
ON CONCAT(t1.sale_date, '-01') = CAST(CONCAT(t2.sale_date, '-01')AS DATE) + INTERVAL 1 YEAR ON CONCAT(t1.sale_date, '-01') = ADD_MONTHS(CONCAT(t2.sale_date, '-01'),12)
Hive实现环比和同比
liuygvip的博客
06-15 1647
环比和同比
数据分析SQL环比增长率、同比增长率
ArYe
11-30 5871
环比 同比
【数据库】HIVE SQL实现本月一号、月底的提取
J小白的博客
01-31 2万+
在工作中很多时候会遇到需要取当前月一月的订单、产量等数据，那么如何实现呢？ 首先，当前日期函数，取名为currentday='${zdt.format("yyyy-MM-dd")}' 例如： 当前日期所在月的一号，取名为currentmontfirstday='${zdt.add(2,0).format("yyyy-MM")}-01' 例如： 当前月的上个月一号，取名为lastm...
【shell】shell调用python脚本--参数传递
J小白的博客
07-12 6766
在工作中，我们会碰到使用shell脚本调用python脚本，那么如何使用？ 1.shell单纯的调用python脚本，不传递参数 shell： file_path="/home/chen_lib" python_name="test.py" python $file_path/$python_name 对的，就这么简单，file_path是你的公共目录，所有文件都放在这个...
【hive】时间函数01
nnnnn68的博客
12-13 2934
月份第一天，最后一天，一月多少天 select trunc('2021-12-13','MM'); select last_day('2021-12-13'); select (datediff(last_day('2021-12-13'), trunc('2021-12-13','MM')) + 1) days; 返回值： 2021-12-01 2021-12-31 31 当天： select current_date() select '${zdt.addDay(-1).format("yyyy
sql Spark 时间处理函数
weixin_43752504的博客
03-06 5464
sql Spark 时间处理函数
数据分层模型（ODS/DWD/DWS/ADS）在实时数仓中如何应用？
最新发布
06-30
<think>我们正在讨论实时数仓中如何应用ODS、DWD、DWS、ADS分层模型。实时数仓要求低延迟的数据处理，因此传统批处理的分层架构需要调整。以下是实时数仓中分层模型的应用方法： 1. **ODS层（实时数据接入层）**：- **应用方式**：使用实时数据采集工具（如FlinkCDC、Kafka Connect）直接捕获业务系统的增量数据变更。数据以流的形式进入ODS层，存储在消息队列（如Kafka）或低延迟存储（如HBase）中。-**场景示例**：电商订单系统实时推送订单创建、支付事件到Kafka的`ods_order_topic`[^1]。 2. **DWD层（实时明细层）**：-**应用方式**：通过流处理引擎（如Flink、Spark Streaming）对ODS层数据进行实时清洗、转换和关联。输出结构化的明细数据流。-**关键操作**：-数据清洗：过滤无效字段（如缺失`user_id`的记录）-数据标准化：统一时间格式为`yyyy-MM-ddHH:mm:ss`-维度关联：实时关联用户维度表（存储在Redis或HBase）-**场景示例**：将订单流与用户信息流实时JOIN，生成包含用户等级的订单明细流`dwd_order_detail`[^3]。 3. **DWS层（实时汇总层）**：-**应用方式**：基于时间窗口（如1分钟/1小时）进行流式聚合计算，生成轻度汇总结果。使用状态存储（如RocksDB）维护中间状态。- **优化策略**：-增量计算：仅处理新到达的数据，避免全量重算-维表预关联：将常用维度提前关联到指标中-**场景示例**：实时计算每5分钟各商品类目的成交金额，输出到`dws_sales_5min`流[^2]。4.**ADS层（实时应用层）**：-**应用方式**：将DWS层的结果写入低延迟数据库（如ClickHouse、Redis），直接支撑前端应用。支持亚秒级查询响应。- **典型场景**：-实时大屏：从ClickHouse读取最新聚合指标-实时告警：基于Flink CEP检测异常流量- **数据模型**：采用宽表结构存储最终结果，例如`ads_realtime_dashboard`包含分钟级GMV、用户数等[^3]。 ###关键技术选型对比|分层|批处理方案|实时方案| |--------|---------------------|------------------------| |ODS|Sqoop+Hive| Kafka+Flink CDC|| DWD| Hive ETL| Flink StreamingSQL| |DWS|Hive聚合+调度|FlinkWindow Agg| |ADS| MySQL/Greenplum|ClickHouse/Redis|###实时数仓分层架构示例```mermaidgraphLRA[业务系统] -->|CDC| B[Kafka ODS]B -->|Flink SQL| C[Kafka DWD]C -->|Flink Window| D[RedisDWS] D-->|API|E[实时大屏 ADS] ```###挑战与解决方案- **挑战1：维表变更****方案**：使用广播流实时同步维度表变更（如FlinkBroadcast State） -**挑战2：精确一次处理****方案**：Flink Checkpoint +事务性写入（如Kafka事务） -**挑战3：历史数据回溯****方案**：Lambda架构（批流结合）或Kappa+（持久化事件日志）> **实时数仓的核心变革**：将传统T+1的分层ETL升级为持续流动的数据管道，通过流处理实现"分层不落地"的数据加工[^1][^3]。 §§相关问题§§1.实时数仓中如何解决维度表变化的缓慢变更维（SCD）问题？2.Flink在实时数仓分层处理中有哪些最佳实践？3.实时数仓的DWS层是否应该使用OLAP数据库替代流处理？</think>### 实时数仓中分层模型（ODS/DWD/DWS/ADS）的应用方法 在实时数仓场景下，传统分层模型需结合流式计算和低延迟存储技术进行重构。以下是各层在实时场景的应用方式和关键技术： #### 1. **ODS层：实时数据接入层** - **应用方式**： - 直接对接业务系统变更日志（如MySQL Binlog、Kafka日志流），**取消HBase存储**[^1]，改用低延迟消息队列（如Kafka/Pulsar） - 采用**CDC工具**（Flink CDC、Debezium）实时捕获增量数据，实现秒级延迟 - **技术实现**： ```sql -- Flink SQL实现MySQL Binlog实时接入 CREATE TABLE ods_order ( id BIGINT, amount DECIMAL, event_time TIMESTAMP ) WITH ( 'connector' = 'mysql-cdc', 'hostname' = 'localhost', 'database-name' = 'mydb', 'table-name' = 'orders' ); ``` #### 2. **DWD层：实时明细层** - **应用方式**： - **流式ETL**：在数据流中进行实时清洗（如过滤无效数据）、格式标准化（时间戳统一为`yyyy-MM-dd HH:mm:ss`）[^1] - **动态维度关联**：通过Flink State或Redis实现流数据与维度表的实时JOIN - **关键操作**： - **会话切割**：对APP日志流按用户活跃状态切分Session（如Flink CEP） - **数据打平**：将嵌套JSON转为宽表结构，适配实时OLAP引擎[^1] - **异常处理**：通过旁路输出流隔离脏数据 #### 3. **DWS层：实时汇总层** - **应用方式**： - **窗口聚合**：基于滚动/滑动窗口（如1分钟/5分钟）计算实时指标 - **多维度预聚合**：通过RoaringBitmap等压缩技术存储用户行为画像[^3] - **技术实现**： ```sql -- Flink SQL实现5分钟交易聚合 CREATE VIEW dws_sales_5min AS SELECT item_id, HOP_END(event_time, INTERVAL '1' MINUTE, INTERVAL '5' MINUTE) AS window_end, SUM(amount) AS total_sales FROM dwd_order_detail GROUP BY item_id, HOP(event_time, INTERVAL '1' MINUTE, INTERVAL '5' MINUTE); ``` - **优化策略**： - **增量计算**：仅处理新到达数据，避免全量重算[^2] - **维表预关联**：将商品类目等维度提前关联到指标 #### 4. **ADS层：实时应用层** - **应用方式**： - **低延迟查询**：将聚合结果写入ClickHouse/Druid等OLAP库，响应延迟<1秒 - **动态指标计算**：在流上直接计算复杂指标（如同比环比） - **典型场景**： - 实时大屏：从DWS层直接读取分钟级GMV、UV等指标 - 实时风控：基于Flink CEP检测异常交易模式 ### 实时数仓分层架构特点 | 分层 | 存储方案 | 计算延迟 | 关键技术 | |-------|-----------------------|------------|--------------------------| | ODS | Kafka/Pulsar | 毫秒级 | CDC, 日志订阅 | | DWD | Kafka + 状态存储 | 秒级 | 流式ETL, 动态维度关联 | | DWS | RedisState/ClickHouse | 亚秒级 | 窗口聚合, 增量计算[^2] | | ADS | ClickHouse/Druid | 毫秒级查询 | 预聚合, 列式存储 | ### 核心挑战与解决方案 1. **维度表实时更新** - 方案：使用**广播状态流**（Broadcast State）同步维度变更 2. **精确一次语义** - 方案：Flink Checkpoint + Kafka事务写入 3. **流批一体** - 方案：Lambda/Kappa+架构，通过**持久化事件日志**支持回溯 > **实时分层核心价值**：将传统T+1的分层ETL升级为**持续流动的数据管道**，实现从原始数据到业务指标的秒级转化[^3]。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值