案例 统计用户上网流量，如果两次上网的时间小于10分钟，合并到一起 --SparkSql实现(SQL风格)

最新推荐文章于 2021-06-16 23:43:11 发布
最新推荐文章于 2021-06-16 23:43:11 发布
阅读量413 收藏 1
点赞数 2
分类专栏： SparkSQL spark 文章标签： spark
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_46959672/article/details/112398063
版权

数据如下

+---+-------------------+-------------------+----+
| id|         start_time|           end_time|flow|
+---+-------------------+-------------------+----+
|  1|2020-02-18 14:20:30|2020-02-18 14:46:30|  20|
|  1|2020-02-18 14:47:20|2020-02-18 15:20:30|  30|
|  1|2020-02-18 15:37:23|2020-02-18 16:05:26|  40|
|  1|2020-02-18 16:06:27|2020-02-18 17:20:49|  50|
|  1|2020-02-18 17:21:50|2020-02-18 18:03:27|  60|
|  2|2020-02-18 14:18:24|2020-02-18 15:01:40|  20|
|  2|2020-02-18 15:20:49|2020-02-18 15:30:24|  30|
|  2|2020-02-18 16:01:23|2020-02-18 16:40:32|  40|
|  2|2020-02-18 16:44:56|2020-02-18 17:40:52|  50|
|  3|2020-02-18 14:39:58|2020-02-18 15:35:53|  20|
|  3|2020-02-18 15:36:39|2020-02-18 15:24:54|  30|
+---+-------------------+-------------------+----+

select
  uid,
  min(start_time) start_time,
  max(end_time) end_time,
  sum(flow) flow
from
(
  select
    uid,
    start_time,
    end_time,
    flow,
    sum(flag) over(partition by uid order by start_time) sum_flag
  from
  (
    select
      uid,
      start_time,
      end_time,
      flow,
      if((to_unix_timestamp(start_time) - to_unix_timestamp(lag_time))/60 > 10, 1,0) flag  -- 将日期转为时间戳，秒为单位
    from
     (
       select
         uid,
         start_time,
         end_time,
         flow,
         lag(end_time,1,start_time) over(partition by uid order by start_time) lag_time
       from
         tb_flowCount
     )
  )
)
group by uid,sum_flag

结果：

+---+-------------------+-------------------+---------+
|uid|         start_time|           end_time|     flow|
+---+-------------------+-------------------+---------+
|  1|2020-02-18 14:20:30|2020-02-18 15:20:30|       50|
|  1|2020-02-18 15:37:23|2020-02-18 18:03:27|      150|
|  3|2020-02-18 14:39:58|2020-02-18 15:35:53|       50|
|  2|2020-02-18 14:18:24|2020-02-18 15:01:40|       20|
|  2|2020-02-18 15:20:49|2020-02-18 15:30:24|       30|
|  2|2020-02-18 16:01:23|2020-02-18 17:40:52|       90|
+---+-------------------+-------------------+---------+

过程解析

1

select
    uid,
    start_time,
    end_time,
    flow,
    lag(end_time,1,start_time) over(partition by uid order by start_time) lag_time  --将上一行的结束时间压到下一行
from
    tb_flowCount

+---+-------------------+-------------------+----+----------------------+
|uid|         start_time|           end_time|flow|              lag_time|
+---+-------------------+-------------------+----+----------------------+
|  1|2020-02-18 14:20:30|2020-02-18 14:46:30|  20|   2020-02-18 14:20:30|
|  1|2020-02-18 14:47:20|2020-02-18 15:20:30|  30|   2020-02-18 14:46:30|
|  1|2020-02-18 15:37:23|2020-02-18 16:05:26|  40|   2020-02-18 15:20:30|
|  1|2020-02-18 16:06:27|2020-02-18 17:20:49|  50|   2020-02-18 16:05:26|
|  1|2020-02-18 17:21:50|2020-02-18 18:03:27|  60|   2020-02-18 17:20:49|
|  3|2020-02-18 14:39:58|2020-02-18 15:35:53|  20|   2020-02-18 14:39:58|
|  3|2020-02-18 15:36:39|2020-02-18 15:24:54|  30|   2020-02-18 15:35:53|
|  2|2020-02-18 14:18:24|2020-02-18 15:01:40|  20|   2020-02-18 14:18:24|
|  2|2020-02-18 15:20:49|2020-02-18 15:30:24|  30|   2020-02-18 15:01:40|
|  2|2020-02-18 16:01:23|2020-02-18 16:40:32|  40|   2020-02-18 15:30:24|
|  2|2020-02-18 16:44:56|2020-02-18 17:40:52|  50|   2020-02-18 16:40:32|
+---+-------------------+-------------------+----+----------------------+

2

select
  uid,
  start_time,
  end_time,
  flow,
  if((to_unix_timestamp(start_time) - to_unix_timestamp(lag_time))/60 > 10, 1,0) flag  -- 将日期转为时间戳，秒为单位
from
(
   select
     uid,
     start_time,
     end_time,
     flow,
     lag(end_time,1,start_time) over(partition by uid order by start_time) lag_time
   from
     tb_flowCount
)

--
+---+-------------------+-------------------+----+----+
|uid|         start_time|           end_time|flow|flag|
+---+-------------------+-------------------+----+----+
|  1|2020-02-18 14:20:30|2020-02-18 14:46:30|  20|   0|
|  1|2020-02-18 14:47:20|2020-02-18 15:20:30|  30|   0|
|  1|2020-02-18 15:37:23|2020-02-18 16:05:26|  40|   1|
|  1|2020-02-18 16:06:27|2020-02-18 17:20:49|  50|   0|
|  1|2020-02-18 17:21:50|2020-02-18 18:03:27|  60|   0|
|  3|2020-02-18 14:39:58|2020-02-18 15:35:53|  20|   0|
|  3|2020-02-18 15:36:39|2020-02-18 15:24:54|  30|   0|
|  2|2020-02-18 14:18:24|2020-02-18 15:01:40|  20|   0|
|  2|2020-02-18 15:20:49|2020-02-18 15:30:24|  30|   1|
|  2|2020-02-18 16:01:23|2020-02-18 16:40:32|  40|   1|
|  2|2020-02-18 16:44:56|2020-02-18 17:40:52|  50|   0|
+---+-------------------+-------------------+----+----+

3

select
  uid,
  start_time,
  end_time,
  flow,
  sum(flag) over(partition by uid order by start_time) sum_flag
from
(
  select
    uid,
    start_time,
    end_time,
    flow,
    if((to_unix_timestamp(start_time) - to_unix_timestamp(lag_time))/60 > 10, 1,0) flag  -- 将日期转为时间戳，秒为单位
  from
   (
     select
       uid,
       start_time,
       end_time,
       flow,
       lag(end_time,1,start_time) over(partition by uid order by start_time) lag_time
     from
       tb_flowCount
   )
)

--+---+-------------------+-------------------+----+--------+
|uid|         start_time|           end_time|flow|sum_flag|
+---+-------------------+-------------------+----+--------+
|  1|2020-02-18 14:20:30|2020-02-18 14:46:30|  20|       0|
|  1|2020-02-18 14:47:20|2020-02-18 15:20:30|  30|       0|
|  1|2020-02-18 15:37:23|2020-02-18 16:05:26|  40|       1|
|  1|2020-02-18 16:06:27|2020-02-18 17:20:49|  50|       1|
|  1|2020-02-18 17:21:50|2020-02-18 18:03:27|  60|       1|
|  3|2020-02-18 14:39:58|2020-02-18 15:35:53|  20|       0|
|  3|2020-02-18 15:36:39|2020-02-18 15:24:54|  30|       0|
|  2|2020-02-18 14:18:24|2020-02-18 15:01:40|  20|       0|
|  2|2020-02-18 15:20:49|2020-02-18 15:30:24|  30|       1|
|  2|2020-02-18 16:01:23|2020-02-18 16:40:32|  40|       2|
|  2|2020-02-18 16:44:56|2020-02-18 17:40:52|  50|       2|
+---+-------------------+-------------------+----+--------+

4

select
  uid,
  min(start_time) start_time,
  max(end_time) end_time,
  sum(flow) flow
from
(
  select
    uid,
    start_time,
    end_time,
    flow,
    sum(flag) over(partition by uid order by start_time) sum_flag
  from
  (
    select
      uid,
      start_time,
      end_time,
      flow,
      if((to_unix_timestamp(start_time) - to_unix_timestamp(lag_time))/60 > 10, 1,0) flag  -- 将日期转为时间戳，秒为单位
    from
     (
       select
         uid,
         start_time,
         end_time,
         flow,
         lag(end_time,1,start_time) over(partition by uid order by start_time) lag_time
       from
         tb_flowCount
     )
  )
)
group by uid,sum_flag
--
+---+-------------------+-------------------+---------+
|uid|         start_time|           end_time|     flow|
+---+-------------------+-------------------+---------+
|  1|2020-02-18 14:20:30|2020-02-18 15:20:30|       50|
|  1|2020-02-18 15:37:23|2020-02-18 18:03:27|      150|
|  3|2020-02-18 14:39:58|2020-02-18 15:35:53|       50|
|  2|2020-02-18 14:18:24|2020-02-18 15:01:40|       20|
|  2|2020-02-18 15:20:49|2020-02-18 15:30:24|       30|
|  2|2020-02-18 16:01:23|2020-02-18 17:40:52|       90|
+---+-------------------+-------------------+---------+
z小丑八怪r
关注
  • 2
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
flume-ng编程之自定义拦截器
zfszhangyuan的博客
09-06 1万+
学习flume以来，实现了日志的多来源自动抽取和多target的自动发送等，但是一直以来的数据清洗过程一直是放在hadoop中用MR程序定时进行清洗的，有没有一种方式编程能够直接在flume中来进行相关的数据清洗数据匹配，过滤掉那些不规范的脏数据，于是决定打这个flume拦截器的主义，觉得只要把代码稍微改改，从拦截body开始自定义intercepter编程完成每个body字符串的解析字段的正则提
ETL_with_Pyspark_-_SparkSQL:一个示例项目，旨在使用Apache Spark中的Pyspark和Spark SQL API演示ETL过程
05-24
ETL_with_Pyspark _-_ SparkSQL 一个示例项目，旨在使用Apache Spark中的Pyspark和Spark SQL API演示ETL过程。 在这个项目中，我使用了Apache Sparks的Pyspark和Spark SQL API来对数据实施ETL过程，最后将转换后的...
案例 统计用户上网流量，如果两次上网的时间小于10分钟，合并到一起 --spark程序实现
小丑八怪的博客
01-01 227
/** * 数据分析： * uid,startTime, endTime, downFlow, lag() over , flag , sum_over * 1,2020-02-18 14:20:30,2020-02-18 14:46:30,20, 2020-02-18 14:20:30 0 0 * 1,2020-02-18 14:47:20,2020-02-18 15:20:30,30, 2020-02-18 14:46:30 0 0 * 1,2020-02-18 15:37:
sparksql经典案例之统计用户上网流量（如果两次上网的时间小于10min，就可以rollup到一起）
qq_44665283的博客
06-16 305
统计用户上网流量，如果两次上网的时间小于10min，就可以rollup到一起 源数据 id start_time end_time flow 1 14:20:30 14:46:30 20 1 14:47:20 15:20:30 30 1 15:37:23 16:05:26 40 1 16:06:27 17:20:49 50 1 17:21:50 18:03:27 60 2 14:18:24 15:01:40 20 2 15:20:49 15:30:24 30 2 16:01:23 16:40:32 40
SparkSQL案例——用SQL和DSL两种语法格式，求出用户登录间隔小于10分钟时一段时间内的总累计流量
LOGAN'S BLOG
10-04 2630
实验数据： uid,sdt,edt,flow 1,2020-02-18 14:20:30,2020-02-18 14:46:30,20 1,2020-02-18 14:47:20,2020-02-18 15:20:30,30 1,2020-02-18 15:37:23,2020-02-18 16:05:26,40 1,2020-02-18 16:06:27,2020-02-18 17:20:49,50 1,2020-02-18 17:21:50,2020-02-18 18:03:27,60 2,2020.
sparkSQL底层实现原理-sparkSQL调优资料包附课件、代码、资料
10-08
SparkSQL是Apache Spark项目的一部分，它提供了一个用于处理结构化和半结构化数据的SQL接口。SparkSQL的主要优点在于它的高性能、可扩展性和易用性，使得数据科学家和开发人员可以方便地在DataFrame和Dataset上执行...
SparkSql实现小案例——代码部分（一）
01-07
一、案例介绍 案例包含三个表：tbDate、tbStock、tbStockDetail。字段信息如下表： 二、要求 1、计算所有订单中每年的销售单数、销售总额 2、计算所有订单每年最大金额订单的销售额 3、计算所有订单中每年最畅销...
crontab shell调用spark-sql，实现周期性动态SQL批量自动执行.rar
12-18
对于熟悉Scala开发的人来说，对于spark-sql的使用，直接jar包中写入代码处理就能轻松实现动态语句的执行。 但是对于我，不打算学习Scala和Java语言，但是又想定时执行时间推延的周期、定时任务，该肿么办？ spark-...
stratio-connector-sparkSQL, ( 废弃的) crossdata连接器，用于触发 SQL.zip
09-18
stratio-connector-sparkSQL, ( 废弃的) crossdata连接器，用于触发 SQL 然而，这个项目已经不再支持 Stratio Crossdata，它是一个利用 Apache Spark 功能的数据联合系统。 Stratio-Connector-SparkSQLStratio ...
跟我一起学Spark之——用户在线时长和登录次数统计
SunWuKong_Hadoop的博客
01-23 1484
package 用户在线时长和登录次数统计 /** * Created by zhoubh on 2016/6/28. */ import java.text.SimpleDateFormat import org.apache.spark.rdd.RDD import org.apache.spark.{SparkConf, SparkContext} import scala.uti...
c#中，比较两个时间相差多少分钟
热门推荐
ychxiaoyixiao的专栏
06-05 3万+
c#中，比较两个时间相差多少分钟 DateTime date1 = new DateTime(); DateTime date2 =new DateTime(); TimeSpan timeSpan = date2 - date1; Console.WriteLine("两次时间相差" + timeSpan.TotalMinutes + "分钟"); 1.DateTime值类型
SQL 看10分钟前的数据
weixin_33973600的博客
12-13 2110
2019独角兽企业重金招聘Python工程师标准>>> ...
sql 合并时间段
jc_benben的专栏
12-30 3016
 有交叉情况： -- 虽然这里是以oracle为实例，但是这种方法是可以应用于mysql等其他数据库 CREATE TABLE T1 (START_TIME DATE,END_TIME DATE); INSERT INTO T1 VALUES(TO_DATE('2016-12-01','YYYY-MM-DD'),TO_DATE('2016-12-05','YYYY-MM-DD')); INSE...
SQL 按分钟合并\统计数据
t101lian的专栏
05-10 856
select min(id) id, min(createTime),sum(价格) 价格总计 from ( select * ,Convert(varchar(13),createTime,121)+ case when datepart(mi,createTime)<=15 then 'aa' when datepart(mi,createTime) between 16 and 30
给出某个时间段，要求以三十分钟为分割，统计出每三十分钟内数据的数量
qq_34307159的博客
10-28 1739
sql写法：看着有点复杂，仔细梳理，发现逻辑是极为简单的,如果有什么不懂，请给小编留言，小编尽量给你们解答 间隔是三十分钟的: SELECT COUNT(DISTINCT tmp.xlh) totalNum, tmp.newTime FROM (SELECT T.xlh xlh, TO_CHAR(T.zhczsj, 'yyyymmddhh24mi') oldT...
spark从入门到精通(五)--sparksql
最新发布
06-28
### 回答1： SparkSQL是Spark生态系统中的一个组件，它提供了一种基于结构化数据的编程接口，可以让用户使用SQL语言来查询和处理数据。SparkSQL支持多种数据源，包括Hive、JSON、Parquet等，同时也支持将查询结果输出到不同的存储系统中。SparkSQL还提供了DataFrame和Dataset两个API，可以让用户以类似于关系型数据库的方式来处理数据。通过SparkSQL，用户可以更方便地进行数据分析和处理，提高数据处理效率和准确性。 ### 回答2： SparkSQL是Spark生态系统中的一部分，能够提供高效的数据处理和快速的查询操作。它支持执行结构化数据处理的SQL语法和DataFrame API，并可与Python、Java、Scala进行交互。 在学习SparkSQL之前，需要掌握Spark基础的RDD编程，理解Spark RDD的概念和操作。接下来，了解SparkSQL的核心组件，即Catalyst Optimizer和Tungsten Execution Engine。 Catalyst Optimizer是SparkSQL的查询优化器，它能够对查询语句进行优化，提高查询速度。它可以通过逻辑优化、物理执行优化和代码生成优化来提高查询效率。 Tungsten Execution Engine是SparkSQL的执行引擎，它通过使用自定义的内存管理和计算支持来提高性能。Tungsten的内存管理器可以在JVM之外使用本地内存，这在处理大型数据集时非常有用。 了解了这些基础概念后，就可以开始学习SparkSQL的语法和操作了。SparkSQL支持的语法类似于传统的SQL语法，但也增加了类似于函数式编程的特性。 在SparkSQL中，数据可以表示为DataFrame或DataSet对象。DataFrame是一个分布式的数据表，类似于传统数据库中的表。DataSet是一个强类型的数据集，可以使用Java或Scala编写类型安全的数据处理逻辑。 SparkSQL还支持连接多个数据源，包括Hive、MySQL、PostgreSQL等。可以使用Spark SQL中的数据源API或JDBC API创建一个JDBC连接并访问数据。 除了基本的查询操作，SparkSQL还提供了许多高级操作，如窗口函数、聚合函数、分组集函数等，这些操作可以帮助用户更高效地处理数据。 最后，还要注意SparkSQL的优化和调试。可以通过查看Spark Web UI、使用count()、explain()函数等方法来进行调试和优化。 总之，SparkSQL是Spark生态系统中的一个重要组成部分，它提供了高效的数据处理和快速的查询操作，是处理和分析大型数据集时的重要工具。 ### 回答3： Spark SQL是Spark生态系统中的一个SQL执行引擎，使用它可以方便的在Spark程序中操作结构化的数据。本文将介绍Spark SQL的使用方法，包括如何使用Spark SQL查询结构化数据、如何使用DataFrame和DataSet API来处理数据，以及如何将DataFrame和DataSet与RDD进行交互。 使用Spark SQL查询结构化数据 Spark SQL通过在Spark程序中使用SQL语句来查询结构化数据。在查询之前，需要加载数据文件并将其转换为DataFrame或DataSet。加载数据文件的方法包括加载文本文件、JSON文件、CSV文件等。加载数据文件后，可以使用SQL语句通过DataFrame或DataSet进行数据查询，并将查询结果打印输出或写入文件。以下是实现这些操作的代码示例： //加载文本文件 val lines = spark.read.textFile("file.txt") //加载JSON文件 val json = spark.read.json("file.json") //加载CSV文件 val csv = spark.read.format("csv").option("header", "true").load("file.csv") //使用SQL语句查询数据 json.createOrReplaceTempView("people") val sqlDF = spark.sql("SELECT * FROM people") //将查询结果打印输出 sqlDF.show() //将查询结果写入文件 sqlDF.write.format("csv").save("result.csv") 使用DataFrame和DataSet API处理数据 Spark SQL提供了DataFrame和DataSet API来处理数据。DataFrame是一种带有命名列的分布式数据集合，DataSet是DataFrame的类型安全版本。使用这些API可以操作DataFrame和DataSet中的列和行数据，并进行转换、聚合和合并等操作。以下是使用DataFrame API操作结构化数据的示例代码： //创建DataFrame val df = spark.read.json("file.json") //显示DataFrame的Schema df.printSchema() //选择特定列进行查询 df.select("name", "age").show() //按name和age进行聚合统计 df.groupBy("name", "age").count().show() //将DataFrame转换为DataSet case class Person(name: String, age: Long) val ds = df.as[Person] //使用DataSet API查询 ds.filter(p => p.age > 18).show() 将DataFrame和DataSet与RDD进行交互 Spark SQL支持DataFrame、DataSet和RDD之间的相互转换。通过这种方式，可以在RDD和DataFrame或DataSet之间进行无缝转换，并执行相应的操作。以下是一些将DataFrame和DataSet与RDD进行交互的示例代码： //将RDD转换为DataFrame val rdd = sc.parallelize(Seq((1, "John"), (2, "Mike"), (3, "Lucy"))) val df = rdd.toDF("id", "name") //将DataFrame转换为RDD val rdd = df.rdd //将DataSet转换为RDD val ds = Seq(Person("John", 23), Person("Mike", 32), Person("Lucy", 18)).toDS() val rdd = ds.rdd 总之，Spark SQL是Spark生态系统中的一个非常有用的工具，通过使用它可以方便地进行数据查询和处理。在使用Spark SQL时，需要理解如何加载和查询数据文件、如何使用DataFrame和DataSet API来处理数据，以及如何将DataFrame和DataSet与RDD进行交互。当然，在实际使用中，还需要根据具体情况进行进一步学习和实践。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值