实时数仓 Hologres 基础

1. 简介

Hologres是一站式实时数据仓库引擎，支持海量数据实时写入、实时更新、实时分析，支持标准SQL（兼容PostgreSQL协议），支持PB级数据多维分析（OLAP）与即席分析（Ad Hoc），支持高并发低延迟的在线数据服务（Serving），与MaxCompute、Flink、DataWorks深度融合，提供离在线一体化全栈数仓解决方案

官方文档 https://help.aliyun.com/zh/hologres/user-guide/developer-guide/?spm=a2c4g.11186623.0.0.29c17ee9xoBSYF

2. 语法

2.1 分组总行数

select COUNT(DISTINCT (tpcode, receiverDistrict))
FROM boot_indicator;

NULL 值也计入

SELECT COUNT(DISTINCT (coalesce(tpCode, 'null'), coalesce(receiverDistrict, 'null')))
FROM boot_indicator;

查询优化：

select count(1) from (
    select count(1) from boot_indicator
    group by tpCode, receiverDistrict
) t;

2.2 limit

语法 LIMIT #{pageSize} OFFSET #{pageStart}
等价 MySQL 的LIMIT pageStart, pageSize

时间和日期转换函数
https://help.aliyun.com/zh/hologres/user-guide/datetime-conversion-functions?spm=a2c4g.11186623.0.0.41516a38nLpBmN

3. Explain 执行计划

文档 https://help.aliyun.com/zh/hologres/user-guide/explain-and-explain-analyze?spm=a2c4g.11186623.0.0.53d5115ckjxgVA

3.1 Count Distinct优化

文档 https://help.aliyun.com/zh/hologres/user-guide/optimize-performance-of-queries-on-hologres-internal-tables?spm=a2c4g.11186623.0.i55#ad8bbe80514my
count(distinct tradeId) as tpCount
优化 UNIQ( tradeId ) AS tpCount