首页 > 其他分享 >clickHouse中实现类似lag和lead的函数

clickHouse中实现类似lag和lead的函数

时间:2023-06-30 17:56:11浏览次数:52  
标签:01 06 lead lag 1970 ASC 2020 ORDER clickHouse

 clickhouse中的lead和lag实现有多种方法,在标准的SQL中使用的windows function即可实现。

示例数据:
CREATE TABLE llexample (
    g Int32,
    a Date )
ENGINE = Memory;
 
INSERT INTO llexample SELECT
    number % 3,
    toDate('2020-01-01') + number
FROM numbers(10);
 
SELECT * FROM llexample ORDER BY g,a;
 
┌─g─┬──────────a─┐
│ 0 │ 2020-01-01 │
│ 0 │ 2020-01-04 │
│ 0 │ 2020-01-07 │
│ 0 │ 2020-01-10 │
│ 1 │ 2020-01-02 │
│ 1 │ 2020-01-05 │
│ 1 │ 2020-01-08 │
│ 2 │ 2020-01-03 │
│ 2 │ 2020-01-06 │
│ 2 │ 2020-01-09 │
└───┴────────────┘

  

方法一:使用常规 window functions进行实现

使用常规窗口函数进行实现sing window functions (starting from Clickhouse 21.3)
SET allow_experimental_window_functions = 1;
 
SELECT
    g,
    a,
    any(a) OVER (PARTITION BY g ORDER BY a ASC ROWS 
                 BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev,
    any(a) OVER (PARTITION BY g ORDER BY a ASC ROWS 
                 BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS next
FROM llexample
ORDER BY
    g ASC,
    a ASC;
 
┌─g─┬──────────a─┬───────prev─┬───────next─┐
│ 0 │ 2020-01-01 │ 1970-01-01 │ 2020-01-04 │
│ 0 │ 2020-01-04 │ 2020-01-01 │ 2020-01-07 │
│ 0 │ 2020-01-07 │ 2020-01-04 │ 2020-01-10 │
│ 0 │ 2020-01-10 │ 2020-01-07 │ 1970-01-01 │
│ 1 │ 2020-01-02 │ 1970-01-01 │ 2020-01-05 │
│ 1 │ 2020-01-05 │ 2020-01-02 │ 2020-01-08 │
│ 1 │ 2020-01-08 │ 2020-01-05 │ 1970-01-01 │
│ 2 │ 2020-01-03 │ 1970-01-01 │ 2020-01-06 │
│ 2 │ 2020-01-06 │ 2020-01-03 │ 2020-01-09 │
│ 2 │ 2020-01-09 │ 2020-01-06 │ 1970-01-01 │
└───┴────────────┴────────────┴────────────┘

  

方法二:使用clickhouse自带的lagInFrame/leadInFrame进行实现

Using lagInFrame/leadInFrame (starting from ClickHouse 21.4)
 
SELECT
    g,
    a,
    lagInFrame(a) OVER (PARTITION BY g ORDER BY a ASC ROWS 
                 BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS prev,
    leadInFrame(a) OVER (PARTITION BY g ORDER BY a ASC ROWS 
                 BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS next
FROM llexample
ORDER BY
    g ASC,
    a ASC;
 
┌─g─┬──────────a─┬───────prev─┬───────next─┐
│ 0 │ 2020-01-01 │ 1970-01-01 │ 2020-01-04 │
│ 0 │ 2020-01-04 │ 2020-01-01 │ 2020-01-07 │
│ 0 │ 2020-01-07 │ 2020-01-04 │ 2020-01-10 │
│ 0 │ 2020-01-10 │ 2020-01-07 │ 1970-01-01 │
│ 1 │ 2020-01-02 │ 1970-01-01 │ 2020-01-05 │
│ 1 │ 2020-01-05 │ 2020-01-02 │ 2020-01-08 │
│ 1 │ 2020-01-08 │ 2020-01-05 │ 1970-01-01 │
│ 2 │ 2020-01-03 │ 1970-01-01 │ 2020-01-06 │
│ 2 │ 2020-01-06 │ 2020-01-03 │ 2020-01-09 │
│ 2 │ 2020-01-09 │ 2020-01-06 │ 1970-01-01 │
└───┴────────────┴────────────┴────────────┘

  

参考:

https://clickhouse.com/docs/zh/sql-reference/window-functions

clickhouse lag/lead_vkingnew的博客-CSDN博客

标签:01,06,lead,lag,1970,ASC,2020,ORDER,clickHouse
From: https://www.cnblogs.com/-courage/p/17517489.html

相关文章

  • CentOS 7 安装ClickHouse
    介绍从官网中看到,ClickHouse支持两种安装方式:存储库安装TAR包安装存储库安装适合能够直接访问到外网的服务器,TAR包适合无法访问外网的服务器。ClickHouse官方提供的有四个包,分别是:clickhouse-common-staticClickHouse编译好的二进制文件clickhouse-server为ClickHouse创建符号链......
  • 反调试专题丨反调试之NtGlobaFlag
    一、32位系统nt!_PEB+0x000InheritedAddressSpace:UChar+0x001ReadImageFileExecOptions:UChar+0x002BeingDebugged:UCharisDbg值,8字节+0x003BitField:UChar+0x003ImageUsesLargePages:Pos0,1Bit+0x003IsProtectedProcess:......
  • Windows下安装ClickHouse图文教程
    @[toc]ClickHouse是没有Windows版的,所以我们要想安装在windows上,一般会选择安装在Docker容器中1.安装WSL2因为在Docker运行的时候需要使用Linux内核,WSL2是Windows10专业版和企业版的功能,它允许在Windows上运行原生的Linux二进制文件。DockerDesktopforWindows使用WS......
  • 003.clickhouse-ck输出所有的表结构
    --clickhouse输出库中表结构--输出所有表名clickhouseclient--host1xx.xxx.xx.xx--userxx_xx--passwordxxxx--multiquery--query="usedefault;showtables;">default_table.txtvimecho_table_ck.sh--输出所有表结构#!/bin/bashecho'usedefault;&......
  • clickhouse数据库里面,类型 datetime, datetime64, datetime(3), datetime64(3)
     问:clickhouse数据库里面,类型datetime,datetime64,datetime(3),datetime64(3)分别是什么意思,举例详细说明 答:在ClickHouse数据库中,有以下几种日期时间类型:DateTime:这是一个8字节的整数类型,表示从公元1年1月1日开始的秒数。它只能存储到秒级别的精度。Dat......
  • ClickHouse数据表迁移实战之-remote方式
    1引言ClickHouse是一个用于联机分析(OLAP)的列式数据库管理系统(DBMS)。我们内部很多的报表、数据看板都基于它进行开发。今天为大家带来remote方式的ClickHouse数据表迁移的完整过程介绍,如有错误,还请各位大佬指正。以下sql语句为测试使用,如需使用请根据实际情况修改。2背景......
  • 【大数据OLAP技术新书推荐】 字节跳动、阿里巴巴大厂资深架构师程序员多年实践经验总
    ClickHouse领域集大成之作-ClickHouse入门进阶实战的标准参考书-日常工作案头必备!如果需要购买阅读的话,可以点击:https://item.jd.com/10077635610807.html目录《ClickHouse入门、实战与进阶》简介图书评价作者简介内容简介为何写作本书本书主要特点如何阅读本书致谢全书目......
  • C# and TypeScript – Enum Flags
    前言以前就有提过EnumFlags,但平日不常用.最近翻Angular源码,发现它很多地方用到,而且没有封装语义代码.一堆符号真的看不惯啊...于是又去复习了一遍,熟悉熟悉.顺便写一篇做记入呗.这篇C#和TypeScript会一起讲. 参考 深入理解TypeScript–枚举Enum,Fl......
  • 【大数据】通过 docker-compose 快速部署 ClickHouse 保姆级教程
    目录一、概述二、ClickHouse列数据存储优缺点1)优点2)缺点三、ClickHouse中Zookeeper的作用四、前期准备1)部署docker2)部署docker-compose五、创建网络六、安装Zookeeper七、ClickHouse编排部署1)下载ClickHouse安装包2)配置3)启动脚本bootstrap.sh4)构建镜像Dockerfile5)编......
  • springboot整合mysql和clickhouse多数据源
    1、添加依赖<!--MyBatis-PlusStarter--><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.2.0</version></dependency>......