Hive分析窗口函数(四) LAG,LEAD,FIRST_VALUE,LAST

Home

Database

Mysql Tutorial

Hive分析窗口函数(四) LAG,LEAD,FIRST_VALUE,LAST_VALUE

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 02:51 PM

firsthivevfunctionanalyzewindow

1.LAG功能是什么？ 2.LEAD与LAG功能有什么相的地方那个？ 3.FIRST_VALUE与LAST_VALUE分别完成什么功能？继续学习这四个分析函数。注意：这几个函数不支持WINDOW子句。 Hive版本为 apache-hive-0.13.1 数据准备：水电费 cookie1,2015-04-10 10:00:02,url2

1.LAG功能是什么？
2.LEAD与LAG功能有什么相似的地方那个？

3.FIRST_VALUE与LAST_VALUE分别完成什么功能？

继续学习这四个分析函数。注意：这几个函数不支持WINDOW子句。 Hive版本为 apache-hive-0.13.1 数据准备：

水电费

    cookie1,2015-04-10 10:00:02,url2
    cookie1,2015-04-10 10:00:00,url1
    cookie1,2015-04-10 10:03:04,1url3
    cookie1,2015-04-10 10:50:05,url6
    cookie1,2015-04-10 11:00:00,url7
    cookie1,2015-04-10 10:10:00,url4
    cookie1,2015-04-10 10:50:01,url5
    cookie2,2015-04-10 10:00:02,url22
    cookie2,2015-04-10 10:00:00,url11
    cookie2,2015-04-10 10:03:04,1url33
    cookie2,2015-04-10 10:50:05,url66
    cookie2,2015-04-10 11:00:00,url77
    cookie2,2015-04-10 10:10:00,url44
    cookie2,2015-04-10 10:50:01,url55

    CREATE EXTERNAL TABLE lxw1234 (
    cookieid string,
    createtime string,  --页面访问时间
    url STRING       --被访问页面
    ) ROW FORMAT DELIMITED
    FIELDS TERMINATED BY ','
    stored as textfile location '/tmp/lxw11/';


    hive> select * from lxw1234;
    OK
    cookie1 2015-04-10 10:00:02     url2
    cookie1 2015-04-10 10:00:00     url1
    cookie1 2015-04-10 10:03:04     1url3
    cookie1 2015-04-10 10:50:05     url6
    cookie1 2015-04-10 11:00:00     url7
    cookie1 2015-04-10 10:10:00     url4
    cookie1 2015-04-10 10:50:01     url5
    cookie2 2015-04-10 10:00:02     url22
    cookie2 2015-04-10 10:00:00     url11
    cookie2 2015-04-10 10:03:04     1url33
    cookie2 2015-04-10 10:50:05     url66
    cookie2 2015-04-10 11:00:00     url77
    cookie2 2015-04-10 10:10:00     url44
    cookie2 2015-04-10 10:50:01     url55

LAG

LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

    SELECT cookieid,
    createtime,
    url,
    ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
    LAG(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS last_1_time,
    LAG(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_2_time
    FROM lxw1234;


    cookieid createtime             url    rn       last_1_time             last_2_time
    -------------------------------------------------------------------------------------------
    cookie1 2015-04-10 10:00:00     url1    1       1970-01-01 00:00:00     NULL
    cookie1 2015-04-10 10:00:02     url2    2       2015-04-10 10:00:00     NULL
    cookie1 2015-04-10 10:03:04     1url3   3       2015-04-10 10:00:02     2015-04-10 10:00:00
    cookie1 2015-04-10 10:10:00     url4    4       2015-04-10 10:03:04     2015-04-10 10:00:02
    cookie1 2015-04-10 10:50:01     url5    5       2015-04-10 10:10:00     2015-04-10 10:03:04
    cookie1 2015-04-10 10:50:05     url6    6       2015-04-10 10:50:01     2015-04-10 10:10:00
    cookie1 2015-04-10 11:00:00     url7    7       2015-04-10 10:50:05     2015-04-10 10:50:01
    cookie2 2015-04-10 10:00:00     url11   1       1970-01-01 00:00:00     NULL
    cookie2 2015-04-10 10:00:02     url22   2       2015-04-10 10:00:00     NULL
    cookie2 2015-04-10 10:03:04     1url33  3       2015-04-10 10:00:02     2015-04-10 10:00:00
    cookie2 2015-04-10 10:10:00     url44   4       2015-04-10 10:03:04     2015-04-10 10:00:02
    cookie2 2015-04-10 10:50:01     url55   5       2015-04-10 10:10:00     2015-04-10 10:03:04
    cookie2 2015-04-10 10:50:05     url66   6       2015-04-10 10:50:01     2015-04-10 10:10:00
    cookie2 2015-04-10 11:00:00     url77   7       2015-04-10 10:50:05     2015-04-10 10:50:01


    last_1_time: 指定了往上第1行的值，default为'1970-01-01 00:00:00'  
                 cookie1第一行，往上1行为NULL,因此取默认值 1970-01-01 00:00:00
                 cookie1第三行，往上1行值为第二行值，2015-04-10 10:00:02
                 cookie1第六行，往上1行值为第五行值，2015-04-10 10:50:01
    last_2_time: 指定了往上第2行的值，为指定默认值
                                                     cookie1第一行，往上2行为NULL
                                                     cookie1第二行，往上2行为NULL
                                                     cookie1第四行，往上2行为第二行值，2015-04-10 10:00:02
                                                     cookie1第七行，往上2行为第五行值，2015-04-10 10:50:01

LEAD

与LAG相反
LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值
第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

    SELECT cookieid,
    createtime,
    url,
    ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
    LEAD(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS next_1_time,
    LEAD(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS next_2_time
    FROM lxw1234;


    cookieid createtime             url    rn       next_1_time             next_2_time
    -------------------------------------------------------------------------------------------
    cookie1 2015-04-10 10:00:00     url1    1       2015-04-10 10:00:02     2015-04-10 10:03:04
    cookie1 2015-04-10 10:00:02     url2    2       2015-04-10 10:03:04     2015-04-10 10:10:00
    cookie1 2015-04-10 10:03:04     1url3   3       2015-04-10 10:10:00     2015-04-10 10:50:01
    cookie1 2015-04-10 10:10:00     url4    4       2015-04-10 10:50:01     2015-04-10 10:50:05
    cookie1 2015-04-10 10:50:01     url5    5       2015-04-10 10:50:05     2015-04-10 11:00:00
    cookie1 2015-04-10 10:50:05     url6    6       2015-04-10 11:00:00     NULL
    cookie1 2015-04-10 11:00:00     url7    7       1970-01-01 00:00:00     NULL
    cookie2 2015-04-10 10:00:00     url11   1       2015-04-10 10:00:02     2015-04-10 10:03:04
    cookie2 2015-04-10 10:00:02     url22   2       2015-04-10 10:03:04     2015-04-10 10:10:00
    cookie2 2015-04-10 10:03:04     1url33  3       2015-04-10 10:10:00     2015-04-10 10:50:01
    cookie2 2015-04-10 10:10:00     url44   4       2015-04-10 10:50:01     2015-04-10 10:50:05
    cookie2 2015-04-10 10:50:01     url55   5       2015-04-10 10:50:05     2015-04-10 11:00:00
    cookie2 2015-04-10 10:50:05     url66   6       2015-04-10 11:00:00     NULL
    cookie2 2015-04-10 11:00:00     url77   7       1970-01-01 00:00:00     NULL

    --逻辑与LAG一样，只不过LAG是往上，LEAD是往下。

FIRST_VALUE

取分组内排序后，截止到当前行，第一个值

    SELECT cookieid,
    createtime,
    url,
    ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
    FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS first1
    FROM lxw1234;

    cookieid  createtime            url     rn      first1
    ---------------------------------------------------------
    cookie1 2015-04-10 10:00:00     url1    1       url1
    cookie1 2015-04-10 10:00:02     url2    2       url1
    cookie1 2015-04-10 10:03:04     1url3   3       url1
    cookie1 2015-04-10 10:10:00     url4    4       url1
    cookie1 2015-04-10 10:50:01     url5    5       url1
    cookie1 2015-04-10 10:50:05     url6    6       url1
    cookie1 2015-04-10 11:00:00     url7    7       url1
    cookie2 2015-04-10 10:00:00     url11   1       url11
    cookie2 2015-04-10 10:00:02     url22   2       url11
    cookie2 2015-04-10 10:03:04     1url33  3       url11
    cookie2 2015-04-10 10:10:00     url44   4       url11
    cookie2 2015-04-10 10:50:01     url55   5       url11
    cookie2 2015-04-10 10:50:05     url66   6       url11
    cookie2 2015-04-10 11:00:00     url77   7       url11

LAST_VALUE

取分组内排序后，截止到当前行，最后一个值

    SELECT cookieid,
    createtime,
    url,
    ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
    LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1
    FROM lxw1234;


    cookieid  createtime            url    rn       last1  
    -----------------------------------------------------------------
    cookie1 2015-04-10 10:00:00     url1    1       url1
    cookie1 2015-04-10 10:00:02     url2    2       url2
    cookie1 2015-04-10 10:03:04     1url3   3       1url3
    cookie1 2015-04-10 10:10:00     url4    4       url4
    cookie1 2015-04-10 10:50:01     url5    5       url5
    cookie1 2015-04-10 10:50:05     url6    6       url6
    cookie1 2015-04-10 11:00:00     url7    7       url7
    cookie2 2015-04-10 10:00:00     url11   1       url11
    cookie2 2015-04-10 10:00:02     url22   2       url22
    cookie2 2015-04-10 10:03:04     1url33  3       1url33
    cookie2 2015-04-10 10:10:00     url44   4       url44
    cookie2 2015-04-10 10:50:01     url55   5       url55
    cookie2 2015-04-10 10:50:05     url66   6       url66
    cookie2 2015-04-10 11:00:00     url77   7       url77

如果不指定ORDER BY，则默认按照记录在文件中的偏移量进行排序，会出现错误的结果

    SELECT cookieid,
    createtime,
    url,
    FIRST_VALUE(url) OVER(PARTITION BY cookieid) AS first2  
    FROM lxw1234;

    cookieid  createtime            url     first2
    ----------------------------------------------
    cookie1 2015-04-10 10:00:02     url2    url2
    cookie1 2015-04-10 10:00:00     url1    url2
    cookie1 2015-04-10 10:03:04     1url3   url2
    cookie1 2015-04-10 10:50:05     url6    url2
    cookie1 2015-04-10 11:00:00     url7    url2
    cookie1 2015-04-10 10:10:00     url4    url2
    cookie1 2015-04-10 10:50:01     url5    url2
    cookie2 2015-04-10 10:00:02     url22   url22
    cookie2 2015-04-10 10:00:00     url11   url22
    cookie2 2015-04-10 10:03:04     1url33  url22
    cookie2 2015-04-10 10:50:05     url66   url22
    cookie2 2015-04-10 11:00:00     url77   url22
    cookie2 2015-04-10 10:10:00     url44   url22
    cookie2 2015-04-10 10:50:01     url55   url22

    SELECT cookieid,
    createtime,
    url,
    LAST_VALUE(url) OVER(PARTITION BY cookieid) AS last2  
    FROM lxw1234;

    cookieid  createtime            url     last2
    ----------------------------------------------
    cookie1 2015-04-10 10:00:02     url2    url5
    cookie1 2015-04-10 10:00:00     url1    url5
    cookie1 2015-04-10 10:03:04     1url3   url5
    cookie1 2015-04-10 10:50:05     url6    url5
    cookie1 2015-04-10 11:00:00     url7    url5
    cookie1 2015-04-10 10:10:00     url4    url5
    cookie1 2015-04-10 10:50:01     url5    url5
    cookie2 2015-04-10 10:00:02     url22   url55
    cookie2 2015-04-10 10:00:00     url11   url55
    cookie2 2015-04-10 10:03:04     1url33  url55
    cookie2 2015-04-10 10:50:05     url66   url55
    cookie2 2015-04-10 11:00:00     url77   url55
    cookie2 2015-04-10 10:10:00     url44   url55
    cookie2 2015-04-10 10:50:01     url55   url55

如果想要取分组内排序后最后一个值，则需要变通一下：

    SELECT cookieid,
    createtime,
    url,
    ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
    LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1,
    FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime DESC) AS last2
    FROM lxw1234
    ORDER BY cookieid,createtime;

    cookieid  createtime            url     rn     last1    last2
    -------------------------------------------------------------
    cookie1 2015-04-10 10:00:00     url1    1       url1    url7
    cookie1 2015-04-10 10:00:02     url2    2       url2    url7
    cookie1 2015-04-10 10:03:04     1url3   3       1url3   url7
    cookie1 2015-04-10 10:10:00     url4    4       url4    url7
    cookie1 2015-04-10 10:50:01     url5    5       url5    url7
    cookie1 2015-04-10 10:50:05     url6    6       url6    url7
    cookie1 2015-04-10 11:00:00     url7    7       url7    url7
    cookie2 2015-04-10 10:00:00     url11   1       url11   url77
    cookie2 2015-04-10 10:00:02     url22   2       url22   url77
    cookie2 2015-04-10 10:03:04     1url33  3       1url33  url77
    cookie2 2015-04-10 10:10:00     url44   4       url44   url77
    cookie2 2015-04-10 10:50:01     url55   5       url55   url77
    cookie2 2015-04-10 10:50:05     url66   6       url66   url77
    cookie2 2015-04-10 11:00:00     url77   7       url77   url77

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

如何使用MySQL数据库进行预测和预测分析？Jul 12, 2023 pm 08:43 PM

如何使用MySQL数据库进行预测和预测分析？概述：预测和预测分析在数据分析中扮演着重要角色。MySQL作为一种广泛使用的关系型数据库管理系统，也可以用于预测和预测分析任务。本文将介绍如何使用MySQL进行预测和预测分析，并提供相关的代码示例。数据准备:首先，我们需要准备相关的数据。假设我们要进行销售预测，我们需要具有销售数据的表。在MySQL中，我们可以使用

如何使用 Go 语言进行数据可视化分析？Jun 10, 2023 am 10:46 AM

随着大数据时代的到来，数据可视化分析在各行各业中扮演着至关重要的角色。而Go语言作为一种快速、高效、安全的编程语言，也逐渐在数据可视化分析领域占据一席之地。本文将探讨如何使用Go语言进行数据可视化分析。一、Go语言常用的数据可视化库Plotly：可用于在浏览器中创建交互式的图形，支持多种图形类型，如线图、条形图、散点图、热力图等。Gonum/plo

Linux下的实时日志监控与分析Jul 29, 2023 am 08:06 AM

Linux下的实时日志监控与分析在日常的系统管理和故障排查中，日志是一个非常重要的数据来源。通过对系统日志的实时监控和分析，我们可以及时发现异常情况并进行相应的处理。本文将介绍Linux下如何进行实时日志监控和分析，并提供相应的代码示例。一、实时日志监控在Linux下，最常用的日志系统是rsyslog。通过配置rsyslog，我们可以实现将不同应用程序的日志

统计分析法的步骤Jun 28, 2023 pm 03:27 PM

统计分析，常指对收集到的有关数据资料进行整理归类并进行解释的过程。统计分析的基本步骤包括：1、收集数据；2、整理数据；3、分析数据。

如何使用PHP进行性能分析和调优Jun 06, 2023 pm 01:21 PM

作为一种流行的服务端语言，PHP在网站开发和运行中扮演着重要的角色。然而，随着PHP代码量的不断增加和应用程序的复杂性提高，性能瓶颈也越来越容易出现。为了避免这种问题，我们需要进行性能分析和调优。本文将简单介绍如何使用PHP进行性能分析和调优，为您的应用程序提供更高效的运行环境。一、PHP性能分析工具1.XdebugXdebug是一款广泛使用的代码分析工具，

最近大火的Diffusion Model，首篇扩散生成模型综述！Apr 09, 2023 pm 08:31 PM

本综述（Diffusion Models: A Comprehensive Survey of Methods and Applications）来自加州大学&Google Research的Ming-Hsuan Yang、北京大学崔斌实验室以及CMU、UCLA、蒙特利尔Mila研究院等众研究团队，首次对现有的扩散生成模型（diffusion model）进行了全面的总结分析，从diffusion model算法细化分类、和其他五大生成模型的关联以及在七大领域中的应用等方面展开，

在Go语言中使用Hive实现高效的数据仓库Jun 15, 2023 pm 08:52 PM

近年来，数据仓库成为了企业数据管理中不可或缺的一部分。直接使用数据库进行数据分析可以满足简单的查询需求，但当我们需要进行大规模数据分析时，单个数据库已经无法满足需求，这时我们需要使用数据仓库来处理海量数据。而Hive则是数据仓库领域中最流行的开源组件之一，它可以将Hadoop分布式计算引擎和SQL查询集成在一起，并支持海量数据的并行处理。同时，在Go语言中使

如何利用PHP和Elasticsearch实现结果聚合和分析Jul 17, 2023 pm 01:05 PM

如何利用PHP和Elasticsearch实现结果聚合和分析引言：随着互联网和信息技术的迅猛发展，数据量的爆炸式增长使得数据的存储、处理、分析变得越来越重要。而Elasticsearch作为一个开源的分布式搜索和分析引擎，具有强大的全文检索、实时分析和数据聚合能力，已经被广泛应用于各大行业中。在本文中，我们将介绍如何利用PHP和Elasticsearch结合

See all articles

Hot AI Tools

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress images for free

Clothoff.io

AI clothes remover

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)

2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

Repo: How To Revive Teammates

1 months agoBy尊渡假赌尊渡假赌尊渡假赌

Hello Kitty Island Adventure: How To Get Giant Seeds

4 weeks agoBy尊渡假赌尊渡假赌尊渡假赌

How Long Does It Take To Beat Split Fiction?

3 weeks agoByDDD

R.E.P.O. Save File Location: Where Is It & How to Protect It?

3 weeks agoByDDD

Hot Tools

SAP NetWeaver Server Adapter for Eclipse

Integrate Eclipse with SAP NetWeaver application server.

MinGW - Minimalist GNU for Windows

This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

VSCode Windows 64-bit Download

A free and powerful IDE editor launched by Microsoft

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

Hot Topics

Where is the login entrance for gmail email?

7338

1627

1352

1265

1210