• clickhouse分组排序后topN
在clickhouse中通过字段进行分组排序最后取所需要的前N条
记录一下分组取TOP N的经验，以后说不定有机会要翻出来看一下当时的思路，在此先声明，本人只是初学者，写的SQL语句很烂。我在这里只是记录一下我在学习过程中遇到的某种案例的解决思路，并不是标准答案
查询要求
按产品维度统计2017年每月的业绩，指标包括 不同品类的采购量、采购额，每个产品里采购量top 10的商品
解决思路
1、先对产品和产品类别里的所有商品进行分组排序处理，分组先后顺序是产品  --> 月份  -->  商品，得出产品维度下每月所有的商品采购额的排序表t，排序方式是将采购额降序处理，即采购额从多到少的方式
SELECT
a1.chanpinID,
a1.chanpinming ,
toYear(a.dingdanriqi) as nian,
toMonth(a.dingdanriqi) as yue,
a.shangpinID,
a.shangpinming ,
SUM(a.caigouliang) as pfshu,
SUM(a.caigoue) as cge
FROM
dingdanbiao a
left join chanpinbiao a1 on a.chanpID = a1.chanpinID
where
toYear(a.dingdanriqi) = 2017
group by
a1.chanpinID ,
a1.chanpinming ,
a.shangpinID ,
a.shangpinming ,
nian,
yue
order by
a1.chanpinID ASC ,
yue,
cge DESC
2、通过clickehouse的groupArray（）函数将t表里的每个产品每个月采购额排名前10的商品的ID存放进groupArray数组里
SELECT
chanpinID,
chanpinming,
nian,
yue,
groupArray(10)(shangpinID) as shpin
from
(SELECT
a1.chanpinID,
a1.chanpinming ,
toYear(a.dingdanriqi) as nian,
toMonth(a.dingdanriqi) as yue,
a.shangpinID,
a.shangpinming ,
SUM(a.caigouliang) as pfshu,
SUM(a.caigoue) as cge
FROM
dingdanbiao a
left join chanpinbiao a1 on a.chanpID = a1.chanpinID
where
toYear(a.dingdanriqi) = 2017
group by
a1.chanpinID ,
a1.chanpinming ,
a.shangpinID ,
a.shangpinming ,
nian,
yue
order by
a1.chanpinID ASC ,
yue,
cge DESC)
group by
chanpinID,
chanpinming,
yue,
nian
order by
chanpinID ,
yue
3、再通过array join将存放在groupArray数组里的shangpinID取出来形成前10商品ID表t1
SELECT
chanpinID,
chanpinming,
nian,
yue,
shpin
FROM
(SELECT
chanpinID,
chanpinming,
nian,
yue,
groupArray(10)(shangpinID) as shpin
from
(SELECT
a1.chanpinID,
a1.chanpinming ,
toYear(a.dingdanriqi) as nian,
toMonth(a.dingdanriqi) as yue,
a.shangpinID,
a.shangpinming ,
SUM(a.caigouliang) as pfshu,
SUM(a.caigoue) as cge
FROM
dingdanbiao a
left join chanpinbiao a1 on a.chanpID = a1.chanpinID
where
toYear(a.dingdanriqi) = 2017
group by
a1.chanpinID ,
a1.chanpinming ,
a.shangpinID ,
a.shangpinming ,
nian,
yue
order by
a1.chanpinID ASC ,
yue,
cge DESC)
group by
chanpinID,
chanpinming,
yue,
nian
order by
chanpinID ,
yue)
ARRAY JOIN shpin
4、最后通过内连接inner join匹配排序表t和前10商品表t1，下面是整体的SQL语句
SELECT
t.chanpinID as pleiID,
t.chanpinming as plei,
t.nian ,
t.yue,
t.shangpinID as spinID,
t.shangpinming as spin,
t.caigouliang,
t.caigoue
FROM
(SELECT
a1.chanpinID,
a1.chanpinming ,
toYear(a.dingdanriqi) as nian,
toMonth(a.dingdanriqi) as yue,
a.shangpinID,
a.shangpinming ,
SUM(a.caigouliang) as pfshu,
SUM(a.caigoue) as cge
FROM
dingdanbiao a
left chanpinbiao a1 on a.chanpID = a1.chanpinID
where
toYear(a.dingdanriqi) = 2017
group by
a1.chanpinID ,
a1.chanpinming ,
a.shangpinID ,
a.shangpinming ,
nian,
yue
order by
a1.chanpinID ASC ,
yue,
cge DESC) t
inner join
(SELECT
chanpinID,
chanpinming,
nian,
yue,
shpin
FROM
(SELECT
chanpinID,
chanpinming,
nian,
yue,
groupArray(10)(shangpinID) as shpin
from
(SELECT
a1.chanpinID,
a1.chanpinming ,
toYear(a.dingdanriqi) as nian,
toMonth(a.dingdanriqi) as yue,
a.shangpinID,
a.shangpinming ,
SUM(a.caigouliang) as pfshu,
SUM(a.caigoue) as cge
FROM
dingdanbiao a
left join chanpinbiao a1 on a.chanpID = a1.chanpinID
where
toYear(a.dingdanriqi) = 2017
group by
a1.chanpinID ,
a1.chanpinming ,
a.shangpinID ,
a.shangpinming ,
nian,
yue
order by
a1.chanpinID ASC ,
yue,
cge DESC)
group by
chanpinID,
chanpinming,
yue,
nian
order by
chanpinID ,
yue)
ARRAY JOIN shpin) t1
on t1.shpin = t.chanpID and t1.yue = t.yue
运行上面的SQL语句后最后输出的结果就是所需要的结果表
总结
我认为这种分组取TOP N的思路大致是一样的：对所需要的数据进行分组排序，用groupArray（）函数对分组后所需的值先取出来存放进数组里，然后通过array join子句将数组里的值都列出来。
我在这里解释一下为什么不使用clickhouse里的topK函数，在官方的技术文档里，topK函数的定义
“返回指定列中近似最常见值的数组，生成的数组按值的近似频率降序排序（而不是值本身），此函数不提供保证的结果。 在某些情况下，可能会发生错误，并且可能会返回不是最高频的值。我们建议使用 N < 10 值，N 值越大，性能越低。最大值 N = 65536。”
topK函数文档：https://clickhouse.tech/docs/zh/sql-reference/aggregate-functions/reference/topk/
也就是说topK虽然高效但不保证最后求值的准确率。对于数据量较少的分组排序取top N时，topK函数的确是不错的选择，但从准确率方面来考虑的话，groupArray函数显然是更好的选择。
至于SQL语句的冗余问题，等SQL技术提高了并且空闲的时候再来修正。
展开全文
• 思路：1、按照股票分组，并按照股票和时间排序。 2、groupArray函数分组数据合并函数，arrayJoin函数可以将array中的元素展开为行 groupArray函数用法说明： groupArray(x)和groupArray(max_si...
下面以股票交易数据为例子：
需求为获取每个股票最后五个交易日的交易数据。ClickHouse不提供窗口函数，那么解决思路变为先找到每个股票的最后五个交易日期，然后再通过股票代码和交易日期关联源表获得相应详细交易数据。
怎嘛解决？
思路：1、按照股票分组，并按照股票和时间排序。
2、groupArray函数分组数据合并函数，arrayJoin函数可以将array中的元素展开为行
groupArray函数用法说明：
groupArray(x)和groupArray(max_size)(x)，数组中元素的顺序就是分组数据中指定的顺序，groupArray(max_size)(x)可以指定数组的大小，max_size即为需求中前N条的数值；
具体实现的SQL语句
SELECT symbol,name,date,open,low,high,close,volume,amount
FROM
(
SELECT symbol,name,date,open,low,high,close,volume,amount
FROM stock_daily
)ALL INNER JOIN(
SELECT symbol,arrayJoin(dates)AS date
FROM
(
SELECT symbol,groupArray(5)(date)AS dates
FROM
(
SELECT symbol,date
FROM stock_daily
ORDER BY symbol,date DESC
)
GROUP BY symbol
)
WHERE dates[1]< toDate('2018-03-16') -- 过滤2018年3月16日停牌中的股票，ClickHouse中数组元素序号从1开始。
)USING symbol,date
SQL实现说明：按照需要分组的字段分组，按照字段排序，然后对非分组字段拼接成数组数据，并按顺序截取数据的前N条数据。最后外层套一层查询，将数组字段拆分成行，就实现了该功能。
系统中应用功能：
实现统计一段时间内区域内每个地铁站的上车总人数，并截取分组排序后每组的前100个。
select
ts.start_region_code ,
ts.up_station_name,
arrayJoin( ts.arr_val) as arr_val,
arrayJoin( ts.total) as total
from
(SELECT
start_region_code,
up_station_name,
groupArray(100)(down_station_name) as arr_val,
groupArray(100)(total) as total,
arrayEnumerate(arr_val) as row_number
from (
select
start_region_code,
up_station_name,
down_station_name,
COUNT(*) as total
from modular.bus_od_base_all boba
where
up_time >='2020-11-01 00:00:00'
and up_time <'2020-11-10 06:00:00'
and down_time >='2020-11-01 00:00:00'
and down_time <'2020-11-10 06:00:00'
GROUP by start_region_code,up_station_name,down_station_name
order by start_region_code,up_station_name, total DESC
)
group by start_region_code,up_station_name
order by start_region_code,up_station_name) ts
where 1=1 order  by ts.start_region_code,total DESC 

以上内容转载于：https://www.jianshu.com/p/c12f6a56a316
展开全文
• 文章目录分组取每组前n分组、汇总同时计算表重命名 分组取每组前n 数据分析分组展示N行想必大家都不陌生，clickhouse也很方便的提供了相关的查询语法：LIMIT n BY express，我们以系统表为例，用到的属性有...


文章目录
分组取每组前n条分组、汇总同时计算表重命名日期类型函数添加修改多个字段

分组取每组前n条
数据分析分组展示N行想必大家都不陌生，clickhouse也很方便的提供了相关的查询语法：LIMIT n BY express，我们以系统表为例，用到的属性有数据库名、表名、表大小。
-- 数据库分组取每个库下最大的三个表
SELECT  database,
table,
FROM system.parts
GROUP BY database,table
ORDER BY database,bytes DESC
limit 3 by database

┌─database───────────────┬─table──────────────────────────┬─bytes──────┐
│ default                │ insert_test_76                 │ 9.93 MiB   │
│ default                │ insert_test_77                 │ 9.92 MiB   │
│ default                │ insert_test_67                 │ 9.42 MiB   │
│ system                 │ trace_log                      │ 8.31 MiB   │
│ system                 │ metric_log                     │ 74.76 MiB  │
│ tutorial               │ hdfs2ch                        │ 5.32 MiB   │
│ tutorial               │ visits_v1                      │ 310.43 MiB │
│ tutorial               │ hits_v1                        │ 1.18 GiB   │
...

结果集会从每个分组中取前三条、如果像system库表容量没有三条则只展示存在的。
分组、汇总同时计算
数据分析时大家是不是常常做了分组统计后、又要再做一个汇总统计。如果这两个才行能合并在一起是不是很方便。ck中我们只需要在sql中加上with totals 就能实现这种效果了。还是以系统表为例。
SELECT  database,
COUNT(table)
FROM system.parts
GROUP BY database
WITH TOTALS

│ tutorial               │ 1.71 GiB                               │            4 │
│ system                 │ 285.54 MiB                             │          228 │
│ default                │ 32.23 GiB                              │         8893 │
│ test                   │ 1.14 GiB                               │           12 │
│ default1               │ 69.57 MiB                              │          232 │
│ test_base              │ 1.55 GiB                               │            3 │
│ test_base_report_view  │ 843.83 MiB                             │            7 │
│ test_base_view         │ 131.55 MiB                             │            2 │
└────────────────────────┴────────────────────────────────────────┴──────────────┘

Totals:
│          │ 37.93 GiB                              │         9381 │
└──────────┴────────────────────────────────────────┴──────────────┘

8 rows in set. Elapsed: 1.163 sec. Processed 9.38 thousand rows, 4.38 MB (8.06 thousand rows/s., 3.77 MB/s.)


表重命名
之前在网上查clickhouse的表重命名需要从A表插入B表，其实ck也是支持表移动的。RENAME可以修改数据表的名称，如果将原始数据库与目标数据库设为不同，那么就可以实现数据表在 两个数据库之间移动的效果。 例如在下面的例子中，testcol_v1从default默认数据库被移动到了db_test 数据库，同时数据表被􏰀命名为testcol_v2:
RENAME TABLE default.testcol_v1 TO db_test.testcol_v2

需要注意的是，数据表的移动只能在单个节点的范围内。换言之，数据表移动的目标数据库和原始数据 库必须处在同一个服务节点内，不能是集群中的远程节点。
日期类型函数
常见的获取当前日期、月初、季度、年份格式转换都是可以在官网-时间日期函数中找到。另外还有一个好用是函数parseDateTimeBestEffort，可以直接很多格式如纯数字类型\$YYYYMMDD直接转换时间。
select parseDateTimeBestEffort('20210101');

┌─parseDateTimeBestEffort('20210101')─┐
│                 2021-01-01 00:00:00 │
└─────────────────────────────────────┘

SELECT parseDateTimeBestEffort('12/12/2020 12:12:57') AS parseDateTimeBestEffort;

┌─parseDateTimeBestEffort─┐
│     2020-12-12 12:12:57 │
└─────────────────────────┘

添加修改多个字段
配置一个或多个用逗号分隔的动作。每个动作是对某个列实施的操作行为。
-- 添加两列

ALTER TABLE test1
ADD COLUMN f1 String,     ADD COLUMN f2 String
Query id: e6452480-faca-440e-b61f-aa517fb41e04

Ok.

0 rows in set. Elapsed: 0.011 sec.

-- 删除两列
ALTER TABLE test1 drop COLUMN f1 , drop COLUMN f2 ;

ALTER TABLE test1
DROP COLUMN f1,     DROP COLUMN f2

Query id: 80c0cdc9-27de-4435-9850-a238088594d0

Ok.

0 rows in set. Elapsed: 0.014 sec.

展开全文
• mysql 我使用排序进行筛选，保证分组、排序之后我的第一条数据就是我要的数据 SELECT a.code , a.type AS 班型 ,MAX(a.num) FROM ( SELECT * FROM cent_ylb_numclass GROUP BY CODE, type ...
MYSQL GROUP BY
mysql  我使用排序进行筛选，保证分组、排序之后我的第一条数据就是我要的数据
SELECT  a.code ,
a.type AS 班型 ,MAX(a.num)
FROM
(
SELECT
*
FROM
cent_ylb_numclass
GROUP BY
CODE,
type
ORDER BY
CODE,
num DESC
) AS a
GROUP BY
code
ORACLE GROUP BY
oracle 使用开窗函数。根据序号取数即可
语法：
ROW_NUMBER() OVER(PARTITION BY COLUMN1 ORDER BY COLUMN2 [desc])
根据column1分组，根据column2排序
此次我们需要根据教师编码code进行分组，教师编码（）code）和教师课程分组数（count（课程分组））降序排序
我们需要确定某个教师教的最多的课程是哪一个
SELECT
localtable.教师编码 as 教师编码,localtable.课程  as 分组
FROM
(SELECT
tablebiao.教师编码 as 教师编码,
tablebiao.教师姓名 as  教师姓名  ,
tablebiao.课程分组 as 课程,
COUNT(tablebiao.课程分组) AS num,
row_number () over (
PARTITION BY tablebiao.教师编码,
tablebiao.教师姓名
ORDER BY
tablebiao.教师编码,
COUNT(tablebiao.课程分组) DESC
) AS rn
FROM
tablebiao
GROUP BY
tablebiao.教师编码,
tablebiao.教师姓名,
tablebiao.课程分组
ORDER BY
tablebiao.教师编码,
COUNT(tablebiao.课程分组) DESC
)  localtable
WHERE
localtable.rn = 1

如有更好的实现方法，或者指导意见请联系微信c243126035
展开全文
• 对于查询的结果，有时候需要到某几行的值/几行的值 首先将查询的结果进行排序 ：比如按照时间，用户id等维度 排序的查询结果如下 datetime user_id action 2020/1/1 12:00 133 view_page 2020...
• 如果以下的内容比较复杂，可以看下我的另一篇，按照自己想要的排序规则取第一条数据： https://blog.csdn.net/u014508939/article/details/100561133 因为在mysql的查询语句顺序上会有先后，先执行group by之后才...
• ClickHouse server version 21.6.6 创建表 dblab-VirtualBox :) CREATE TABLE limit_by(id Int, val Int) ENGINE = Memory; CREATE TABLE limit_by ( id Int, val Int ) ENGINE = Memory Query id: 892b...
• 需求如下： 表flight_sale_history： ...要求对flight_sale_history中的每一记录，求出discount_cabin中折扣最接近的舱位数组的第一个舱位。 想法： 1. 将discount_cabin变成每个air_code一记录，...
• 一、数值运算 sum 简单求和 sumWithOverflow 同步求和参数的数据类型，只适用于数字 sumIf 条件求和 ...三、分组 ...如果有多个arg对应同一个最小值val，遇到的第一个值，argMax同理 -- 应用 SEL
• 背景：项目需要使用clickhouse及mysql数据库，且两个库中的表需要关联查询。 .MySQL中使用 group by 说明：理论上select 所查询的列 都需要加在group by 后面，或者使用sum()、min()等聚合函数 1.MySQL中使用...
• 需要分组后查询出来的是每个组的最大值，这里就需要用到先排序再分组。 例子： select createdate , userid , houseid from t_house_houselock where id in (select   SUBSTRING_INDEX( GROUP_CONCAT(id ORDER ...
• hbase：数据库、可存储海量数据、不太支持sql、存k-vvalue比较快、不适合分析做报表 hive：数仓工具，底层转成mr程序处理和分析数据、良好的SQL语法、便于分析做报表、但是慢 clickhouse：存储海量数据、快速查询...
• ## Clickhouse基础知识

千次阅读 2020-09-27 15:56:07
Clickhouse快速入门 本文参考以下博文： https://clickhouse.tech/docs/zh/ 【clickhouse中文社区文档】 https://www.jianshu.com/p/5f7809b1965e ....Clickhouse简介 Clic
• Block：数据块，ClickHouse进行数据读、写的基本单元，每个Block实例，不仅包含数据域，还包含了每个列的meta信息。 Chunk：数据块，保存实际数据的单元，Block中的数据域的指向的就是这个类型的实例。 Row：行...
• Clickhouse 数据类型 ...1 整数 有符号整数Int<位数> 名称 范围 大概范围 对应MySQL数据类型 Int8 -128 ~ 127 百 Tinyint Int16 -32768 ~32767 万 Smallint Int32 -2147483648 ~ 21474
• MergeTree的主键（PRIMARYKEY）只是用来生成级索引（primary.idx）的，并没有唯一性约束这样的语义。 ReplacingMergeTree通过ORDERBY，表示判断唯一约束的条件。当分区合并之时，根据ORDERBY排序后，相邻重复的...
• clickhouse系列】ClickHouse入门 1、官方文档 猛戳这里 2、ClickHouse入门指北 猛戳这里---------这个很重要，下面写的很多东西都来自于这个文档的理论基础。 3、ClickHouse源码 猛戳这里---------c++写的，暂时...
• 本篇来自数月前对外分享的文稿整理，并进行了一些扩展。 希望通过简单的方式，来介绍新手如何一步一步上手 ClickHouse，如果你有潜在的数据分析的需求，但是不知道...感谢两年前位好朋友对我进行的技术选型推荐，使用
• 1.介绍 常见的行式数据库系统有： MySQL、Postgres和MS SQL ...1) 针对分析类查询，通常只需要读取表的小部分列。例如，如果只需要读取100列中的5列，这将帮助你最少减少20倍的I/O消耗。 2) 由于数据总是打包...
• 学习笔记（二）ClickHouse的数据类型 作为款分析型
• 、what is clickhouseclickhouse 诞生于俄罗斯最大的搜索公司Yandex，在clickhouse的配置文件中我们也会看到yandex的影子。 clickhouse 是面向联机分析处理（OLAP, On-Line Analytical Processing） 的...
• 1.limit 2.select 正则表达式 3.ORDER BY子句 4.DISTINCT 5.UNION ALL子句
• 这里写自定义目录标题前言表结构及查询需求ClickHouse中的方法 前言 平时 SQL 用得少，最近刚好需要在 Metabase 上用与 SQL 差不多的 ClickHouse 语句搭数据仪表盘，于是遇到了以下问题，在查询时，需要按两个字段...
• ClickHouse已经为Yandex.Metrica存储了超过20万亿行的数据，90%的自定义查询能够在1秒内返回，其集群规模也超过了400台服务器。虽然ClickHouse起初只是为了Yandex.Metrica而研发的，但由于它出众的性能，目前也被...
• 第1ClickHouse概述 1.1 什么是ClickHouse ClickHouse个开源的，面向列的分析数据库，由Yandex为OLAP和大数据用例创建。 ClickHouse对实时查询处理的支持使其适用于需要亚秒级分析结果的应用程序。 ...
• 前言前面通过一文了解ClickHouse 介绍过ClickHouse，特性，结构，使用场景。自己并未完全深入学习clickhouse，因为公司打算小范围使用ClickHouse，所以...
• 章节目录、WITH子句1.1、定义变量1.2、调用函数1.3、定义子查询1.4、在子查询中重复使用WITH二、FROM子句1.1、从数据表中数1.2、从子查询中数1.3、从表函数中数三、SAMPLE子句1.1、SAMPLE factor 作为款...
• 最近基于ClickHouse做了一些统计，用到...1、先做个子查询过滤下数据，按照userId、eventDate分组再按照时间排序，同时将时间字段(eventData)转为1到366的数字 select userId, toDayOfYear(eventDate) as d from db_t

...