文档章节

presto函数

hutaishi
 hutaishi
发布于 01/24 14:33
字数 692
阅读 63
收藏 0

CASE

CASE expression
    WHEN value THEN result
    [ WHEN ... ]
    [ ELSE result ]
END

SELECT a,
       CASE a
           WHEN 1 THEN 'one'
           WHEN 2 THEN 'two'
           ELSE 'many'
       END

 

CASE
    WHEN condition THEN result
    [ WHEN ... ]
    [ ELSE result ]
END

SELECT a, b,
       CASE
           WHEN a = 1 THEN 'aaa'
           WHEN b = 2 THEN 'bbb'
           ELSE 'ccc'
       END

IF

The IF function is actually a language construct that is equivalent to the following CASE expression:

CASE
    WHEN condition THEN true_value
    [ ELSE false_value ]
END

if(conditiontrue_value)

Evaluates and returns true_value if condition is true, otherwise null is returned and true_value is not evaluated.

if(conditiontrue_valuefalse_value)

Evaluates and returns true_value if condition is true, otherwise evaluates and returns false_value.

COALESCE

coalesce(value1value2[, ...])

Returns the first non-null value in the argument list. Like a CASE expression, arguments are only evaluated if necessary.

Window Functions

Window functions perform calculations across rows of the query result. They run after the HAVING clause but before the ORDER BY clause. Invoking a window function requires special syntax using the OVER clause to specify the window. A window has three components:

  • The partition specification, which separates the input rows into different partitions. This is analogous to how the GROUP BY clause separates rows into different groups for aggregate functions.
  • The ordering specification, which determines the order in which input rows will be processed by the window function.
  • The window frame, which specifies a sliding window of rows to be processed by the function for a given row. If the frame is not specified, it defaults to RANGE UNBOUNDED PRECEDING, which is the same as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This frame contains all rows from the start of the partition up to the last peer of the current row.

For example, the following query ranks orders for each clerk by price:

SELECT orderkey, clerk, totalprice,
       rank() OVER (PARTITION BY clerk
                    ORDER BY totalprice DESC) AS rnk
FROM orders
ORDER BY clerk, rnk

rank() → bigint

Returns the rank of a value in a group of values. The rank is one plus the number of rows preceding the row that are not peer with the row. Thus, tie values in the ordering will produce gaps in the sequence. The ranking is performed for each window partition.

row_number() → bigint

Returns a unique, sequential number for each row, starting with one, according to the ordering of rows within the window partition.

如果  ... over(  ... order by....)中不存在排名相同的,那么rank()和row_number()效果相同,若存在排名相同的,rank()会出现1,1,3,3这样的排名,row_number则是1,2,3,4

Aggregate Functions

All Aggregate Functions can be used as window functions by adding the OVER clause. The aggregate function is computed for each row over the rows within the current row’s window frame.

For example, the following query produces a rolling sum of order prices by day for each clerk:

SELECT clerk, orderdate, orderkey, totalprice,
       sum(totalprice) OVER (PARTITION BY clerk
                             ORDER BY orderdate) AS rolling_sum
FROM orders
ORDER BY clerk, orderdate, orderkey

WITH Clause

The WITH clause defines named relations for use within a query. It allows flattening nested queries or simplifying subqueries. For example, the following queries are equivalent:

SELECT a, b
FROM (
  SELECT a, MAX(b) AS b FROM t GROUP BY a
) AS x;

WITH x AS (SELECT a, MAX(b) AS b FROM t GROUP BY a)
SELECT a, b FROM x;

This also works with multiple subqueries:

WITH
  t1 AS (SELECT a, MAX(b) AS b FROM x GROUP BY a),
  t2 AS (SELECT a, AVG(d) AS d FROM y GROUP BY a)
SELECT t1.*, t2.*
FROM t1
JOIN t2 ON t1.a = t2.a;

Additionally, the relations within a WITH clause can chain:

WITH
  x AS (SELECT a FROM t),
  y AS (SELECT a AS b FROM x),
  z AS (SELECT b AS c FROM y)
SELECT c FROM z;

 

© 著作权归作者所有

hutaishi
粉丝 5
博文 72
码字总数 82359
作品 0
广州
程序员
私信 提问
Presto介绍与常用查询优化方法

Presto Hive使用MapReduce作为底层计算框架,是专为批处理设计的。但随着数据越来越多,使用Hive进行一个简单的数据查询可能要花费几分到几小时,显然不能满足交互式查询的需求。 2012年秋季...

高广超
2018/10/10
0
0
使用presto数据库在字符数字比较中遇到的坑

1.事情的始末 公司的sql查询平台提供了HIVE和Presto两种查询引擎来查询hive中的数据,由于presto的速度较快,一般能用presto跑就不用hive跑(有的时候如果使用了hive的UDF就必须用hive了),...

Meet相识_bfa5
2018/05/15
0
0
presto执行过程,sql支持及hive异同

Prestodb概述及性能测试 博客分类: presto系列 概述内容 (1)简介 (2)Hive and Prestodb, comparison of functionality (3)Hive and Prestodb, comparison of performance (1)简介 ...

hblt-j
02/14
161
0
大数据实时查询-Presto集群部署搭建

Presto介绍 Presto是一个分布式SQL查询引擎, 它被设计为用来专门进行高速、实时的数据分析。它支持标准的ANSI SQL,包括复杂查询、聚合(aggregation)、连接(join)和窗口函数(window fu...

高广超
2018/10/11
0
0
Presto 0.175 发布,Facebook 大数据查询引擎

Presto 0.175 发布了,Presto 是 Facebook 开源的数据查询引擎,可对250PB以上的数据进行快速地交互式分析,查询的速度达到商业数据仓库的级别。据称该引擎的性能是 Hive 的 10 倍以上。 Pr...

王练
2017/05/08
1K
3

没有更多内容

加载失败,请刷新页面

加载更多

OSChina 周二乱弹 —— 他只能用这个办法劝你注意身体了

Osc乱弹歌单(2019)请戳(这里) 【今日歌曲】 @-冰冰棒- :#今日歌曲推荐# Kodaline《High Hopes》 《High Hopes》- Kodaline 手机党少年们想听歌,请使劲儿戳(这里) @xiaoshiyue :仙女...

小小编辑
13分钟前
415
10
Spring Boot Actuator 整合 Prometheus

简介 Spring Boot 自带监控功能 Actuator,可以帮助实现对程序内部运行情况监控,比如监控状况、Bean加载情况、环境变量、日志信息、线程信息等。这一节结合 Prometheus 、Grafana 来更加直观...

程序员果果
22分钟前
3
0
Linux文件查找命令详解

对于文件查找,我们最好用的还是属于find命令了,在说find命令之前,先把另外几个查找命令介绍一下。 目录 0x01 查询命令介绍 0x02 find命令介绍 0x01 查询命令介绍 在介绍之前,首先先了解一...

无心的梦呓
22分钟前
3
0
快速掌握的测试用例优先级划分方法

怎么样的设计才能算测试用例 引自:IEEE Standard 610 (1990): A set of test inputs, execution conditions, and expected results developed for a particular objective, such as to exe......

测者陈磊
25分钟前
3
0
[mycat]Attribute value "roadNodeId,version" of type NMTOKEN must be a name token

不能逗号配两个字段的主键 primaryKey="roadNodeId,version" Caused by: io.mycat.config.util.ConfigException: org.xml.sax.SAXParseException; lineNumber: 7; columnNumber: 105; Attrib......

Danni3
31分钟前
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部