数据分析工具：Presto：Presto数据类型与函数

数据分析工具：Presto：Presto数据类型与函数1Presto数据类型概览1.1基本数据类型介绍在Presto中，基本数据类型是构建查询和处理数据的基石。这些类型包括数值类型、字符串类型、日期和时间类型、布尔类型等，下面将详细介绍每种类型。1.1.1数值类型Presto支持多种数值类型，包括整数类型和浮点数类型。整数类型：tinyint（8位）、smallint（16位）、integer（32位）、bigint（64位）。浮点数类型：real（32位）、double（64位）。示例--创建一个包含整数和浮点数的表

CREATETABLEnumbers(

idinteger,

valuedouble

);

--插入数据

INSERTINTOnumbers(id,value)

VALUES(1,3.14);

--查询数据

SELECT*FROMnumbers;1.1.2字符串类型字符串类型用于存储文本数据，包括varchar和char。varchar：变长字符串，适合存储长度不固定的文本。char：定长字符串，适合存储长度固定的文本。示例--创建一个包含字符串的表

CREATETABLEnames(

namevarchar(50)

);

--插入数据

INSERTINTOnames(name)

VALUES('张三');

--查询数据

SELECT*FROMnames;1.1.3日期和时间类型Presto提供了处理日期和时间的类型，包括date、time、timestamp和timestampwithtimezone。示例--创建一个包含日期和时间的表

CREATETABLEevents(

event_datedate,

event_timetime,

event_timestamptimestamp

);

--插入数据

INSERTINTOevents(event_date,event_time,event_timestamp)

VALUES('2023-01-01','12:00:00','2023-01-0112:00:00');

--查询数据

SELECT*FROMevents;1.1.4布尔类型布尔类型用于存储逻辑值，包括boolean。示例--创建一个包含布尔值的表

CREATETABLEflags(

is_activeboolean

);

--插入数据

INSERTINTOflags(is_active)

VALUES(true);

--查询数据

SELECT*FROMflags;1.2复杂数据类型解析复杂数据类型允许在Presto中存储更复杂的数据结构，如数组、映射和结构体。1.2.1数组类型数组类型用于存储相同类型的数据集合。示例--创建一个包含数组的表

CREATETABLEarray_table(

idinteger,

valuesarray(integer)

);

--插入数据

INSERTINTOarray_table(id,values)

VALUES(1,ARRAY[1,2,3]);

--查询数据

SELECT*FROMarray_table;1.2.2映射类型映射类型用于存储键值对。示例--创建一个包含映射的表

CREATETABLEmap_table(

idinteger,

datamap(varchar,integer)

);

--插入数据

INSERTINTOmap_table(id,data)

VALUES(1,MAP('key1',1,'key2',2));

--查询数据

SELECT*FROMmap_table;1.2.3结构体类型结构体类型用于存储一组命名的字段。示例--创建一个包含结构体的表

CREATETABLEstruct_table(

idinteger,

personrow(first_namevarchar,last_namevarchar)

);

--插入数据

INSERTINTOstruct_table(id,person)

VALUES(1,ROW('李','四'));

--查询数据

SELECT*FROMstruct_table;1.3数据类型转换方法在Presto中，可以使用内置函数进行数据类型转换，以适应不同的查询需求。1.3.1CAST函数CAST函数是最常用的类型转换函数，可以将一种类型转换为另一种类型。示例--创建一个包含不同数据类型的表

CREATETABLEmixed_types(

idinteger,

namevarchar,

datedate

);

--插入数据

INSERTINTOmixed_types(id,name,date)

VALUES(1,'王五','2023-01-01');

--使用CAST函数转换数据类型

SELECTCAST(idASvarchar)ASid_str,CAST(dateASvarchar)ASdate_str

FROMmixed_types;1.3.2TRY_CAST函数TRY_CAST函数尝试进行类型转换，如果转换失败，则返回NULL。示例--使用TRY_CAST函数尝试转换数据类型

SELECTTRY_CAST('123'ASinteger)ASint_value,

TRY_CAST('abc'ASinteger)ASint_value

FROMmixed_types;1.3.3其他类型转换函数Presto还提供了针对特定类型转换的函数，如to_timestamp、to_date等。示例--使用to_timestamp函数转换字符串为时间戳

SELECTto_timestamp('2023-01-0112:00:00','yyyy-MM-ddHH:mm:ss')AStimestamp_value

FROMmixed_types;通过上述介绍和示例，我们可以看到Presto提供了丰富且灵活的数据类型，以及类型转换方法，这使得在处理复杂数据时更加得心应手。在实际应用中，根据数据的特性和查询需求选择合适的数据类型和转换方法，可以提高查询效率和数据处理的准确性。2Presto函数基础2.1聚合函数详解Presto提供了一系列强大的聚合函数，用于对数据集进行汇总分析。这些函数可以处理数值、字符串、日期等多种数据类型，是进行数据分析时不可或缺的工具。2.1.1COUNTCOUNT函数用于计算非空值的数量。例如，统计表中某列的非空记录数：SELECTCOUNT(column_name)FROMtable_name;如果要统计所有记录，无论值是否为空，可以使用COUNT(*)：SELECTCOUNT(*)FROMtable_name;2.1.2SUMSUM函数用于计算数值列的总和。例如，计算销售总额：SELECTSUM(sales)FROMsales_table;2.1.3AVGAVG函数用于计算数值列的平均值。例如，计算平均销售价格：SELECTAVG(price)FROMsales_table;2.1.4MIN和MAXMIN和MAX函数分别用于找出数值列中的最小值和最大值。例如，找出最低和最高销售价格：SELECTMIN(price),MAX(price)FROMsales_table;2.1.5示例假设我们有一个销售数据表sales，包含以下数据：sales_idproduct_idsales_datesales_amount11012023-01-0120021022023-01-0230031032023-01-0340041042023-01-0450051052023-01-05NULL我们可以使用以下查询来获取销售总额、平均销售金额、最低和最高销售金额以及非空销售记录的数量：SELECT

SUM(sales_amount)AStotal_sales,

AVG(sales_amount)ASaverage_sales,

MIN(sales_amount)ASmin_sales,

MAX(sales_amount)ASmax_sales,

COUNT(sales_amount)ASnon_null_sales

FROMsales;2.2字符串函数操作Presto支持多种字符串函数，用于处理和操作字符串数据。2.2.1CONCATCONCAT函数用于连接两个或多个字符串。例如，将名字和姓氏连接起来：SELECTCONCAT(first_name,'',last_name)ASfull_nameFROMusers;2.2.2SUBSTRINGSUBSTRING函数用于从字符串中提取子串。例如，提取电子邮件地址中的域名：SELECTSUBSTRING(email,POSITION('@'INemail)+1)ASdomainFROMusers;2.2.3LOWER和UPPERLOWER和UPPER函数用于将字符串转换为小写或大写。例如，将所有产品名称转换为大写：SELECTUPPER(product_name)ASproduct_name_upperFROMproducts;2.2.4示例假设我们有一个用户数据表users，包含以下数据：user_idfirst_namelast_nameemail1JohnDoejohn.doe@2JaneDoejane.doe@3MikeSmithmike.smith@我们可以使用以下查询来获取每个用户的全名、电子邮件的域名，并将所有名字转换为大写：SELECT

CONCAT(first_name,'',last_name)ASfull_name,

SUBSTRING(email,POSITION('@'INemail)+1)ASdomain,

UPPER(first_name)ASfirst_name_upper

FROMusers;2.3日期和时间函数应用Presto提供了丰富的日期和时间函数，用于处理和分析时间序列数据。2.3.1DATE_ADD和DATE_SUBDATE_ADD和DATE_SUB函数用于在日期上增加或减少指定的时间间隔。例如，计算一个月后的日期：SELECTDATE_ADD('MONTH',1,order_date)ASnext_month_order_dateFROMorders;2.3.2EXTRACTEXTRACT函数用于从日期时间中提取特定部分，如年、月、日等。例如，提取订单日期的年份：SELECTEXTRACT(YEARFROMorder_date)ASorder_yearFROMorders;2.3.3示例假设我们有一个订单数据表orders，包含以下数据：order_idorder_date12023-01-0110:0022023-01-0211:0032023-01-0312:0042023-01-0413:0052023-01-0514:00我们可以使用以下查询来计算每个订单一个月后的日期，并提取订单日期的年份和月份：SELECT

order_id,

DATE_ADD('MONTH',1,order_date)ASnext_month_order_date,

EXTRACT(YEARFROMorder_date)ASorder_year,

EXTRACT(MONTHFROMorder_date)ASorder_month

FROMorders;通过上述示例，我们可以看到Presto的函数在处理数据时的强大和灵活性，无论是进行数值的汇总分析，字符串的处理，还是日期时间的计算，都能提供高效且精确的解决方案。3高级数据处理技巧3.1窗口函数的使用窗口函数在数据分析中非常强大，它们允许我们在数据集的特定窗口内执行计算，而无需进行子查询或自连接。在Presto中，窗口函数可以用于计算排名、行数、累计和、移动平均等。3.1.1排名函数示例：row_number(),rank(),dense_rank()假设我们有一个销售数据表sales，包含sale_id,product_id,sale_date,和amount字段。我们想要计算每个产品在所有销售记录中的排名。--使用row_number()函数

SELECTproduct_id,amount,

row_number()OVER(ORDERBYamountDESC)asrow_num

FROMsales;

--使用rank()函数

SELECTproduct_id,amount,

rank()OVER(ORDERBYamountDESC)asrank_num

FROMsales;

--使用dense_rank()函数

SELECTproduct_id,amount,

dense_rank()OVER(ORDERBYamountDESC)asdense_rank_num

FROMsales;3.1.2分组窗口函数示例：sum(),avg(),count()over窗口如果我们想要计算每个产品在每个月的总销售额，可以使用sum()窗口函数。SELECTproduct_id,date_trunc('month',sale_date)asmonth,

sum(amount)OVER(PARTITIONBYproduct_id,date_trunc('month',sale_date))asmonthly_sales

FROMsales;3.1.3累积和与移动平均示例：sum(),avg()累积和与移动平均计算每个产品累积销售额和过去3个月的移动平均销售额。--累积和

SELECTproduct_id,sale_date,amount,

sum(amount)OVER(PARTITIONBYproduct_idORDERBYsale_date)ascumulative_sales

FROMsales;

--移动平均

SELECTproduct_id,sale_date,amount,

avg(amount)OVER(PARTITIONBYproduct_idORDERBYsale_dateROWSBETWEEN2PRECEDINGANDCURRENTROW)asmoving_avg

FROMsales;3.2JSON和数组函数演示Presto支持处理JSON数据和数组，这对于处理复杂数据结构非常有用。3.2.1JSON函数示例：json_parse(),json_extract(),json_extract_scalar()假设我们有一个包含JSON数据的字段data，其中存储了产品信息。--解析JSON数据

SELECTjson_parse(data)asparsed_data

FROMproducts;

--提取JSON数据中的字段

SELECTjson_extract(parsed_data,'$.name')asproduct_name

FROM(SELECTjson_parse(data)asparsed_dataFROMproducts)subquery;

--提取JSON数据中的标量值

SELECTjson_extract_scalar(parsed_data,'$.price')asproduct_price

FROM(SELECTjson_parse(data)asparsed_dataFROMproducts)subquery;3.2.2数组函数示例：array_construct(),array_length(),array_contains()如果我们想要创建一个包含多个产品ID的数组，并检查某个产品ID是否在数组中。--创建数组

SELECTarray_construct(1,2,3,4)asproduct_ids;

--计算数组长度

SELECTarray_length(product_ids)asarray_length

FROM(SELECTarray_construct(1,2,3,4)asproduct_ids)subquery;

--检查数组是否包含特定元素

SELECTarray_contains(product_ids,3)ascontains_3

FROM(SELECTarray_construct(1,2,3,4)asproduct_ids)subquery;3.3地理空间函数介绍地理空间函数在处理地理位置数据时非常有用，可以进行距离计算、地理编码等。3.3.1创建地理空间点示例：ST_Point()--创建地理空间点

SELECTST_Point(121.4737,31.2304)aslocation;3.3.2计算两点之间的距离示例：ST_Distance()假设我们有两个地理空间点location1和location2，我们想要计算它们之间的距离。--计算两点之间的距离

SELECTST_Distance(location1,location2)asdistance

FROM(

SELECTST_Point(121.4737,31.2304)aslocation1,

ST_Point(121.5389,31.1934)aslocation2

)subquery;3.3.3地理编码示例：ST_Geocode()--地理编码

SELECTST_Geocode('上海市浦东新区')aslocation;请注意，ST_Geocode()函数可能需要特定的地理空间数据支持，且在某些Presto版本中可能不可用。以上示例展示了如何在Presto中使用窗口函数、JSON和数组函数以及地理空间函数进行高级数据处理。这些函数极大地增强了SQL查询的能力，使得复杂的数据分析任务变得更加简单和高效。4函数与数据类型实战4.1数据清洗与预处理在数据分析的初期阶段，数据清洗与预处理是至关重要的步骤。Presto提供了丰富的函数来帮助我们处理数据，确保数据的质量和一致性。4.1.1处理空值和缺失值Presto中的IFNULL和COALESCE函数可以用来处理空值或缺失值。示例：使用IFNULL函数--假设我们有一个包含销售数据的表sales，其中price字段可能有空值

SELECTIFNULL(price,0)ASprice,quantity

FROMsales;在这个例子中，如果price字段为空，IFNULL函数会将其替换为0。示例：使用COALESCE函数--假设我们有两个字段，price1和price2，其中一个可能为空

SELECTCOALESCE(price1,price2,0)ASprice

FROMsales;COALESCE函数会返回第一个非空的参数，如果所有参数都为空，则返回最后一个参数。4.1.2字符串处理Presto提供了多种字符串处理函数，如TRIM,REPLACE,REGEXP_REPLACE等。示例：使用TRIM函数--假设我们有一个包含产品名称的字段，其中可能包含前导或尾随空格

SELECTTRIM(both''FROMproduct_name)ASclean_name

FROMproducts;TRIM函数可以去除字符串前后的空格。示例：使用REPLACE函数--假设我们想要将产品描述中的所有逗号替换为分号

SELECTREPLACE(product_description,',',';')ASclean_description

FROMproducts;REPLACE函数可以将字符串中的一个子串替换为另一个子串。4.1.3日期和时间处理Presto提供了日期和时间处理函数，如DATE_TRUNC,DATE_ADD,DATE_DIFF等。示例：使用DATE_TRUNC函数--假设我们有一个包含订单日期的字段，我们想要按月分组

SELECTDATE_TRUNC('month',order_date)ASmonth,COUNT(*)ASorders

FROMorders

GROUPBYmonth;DATE_TRUNC函数可以将日期截断到指定的时间单位。4.2数据分析与挖掘Presto的统计和聚合函数在数据分析与挖掘中扮演着重要角色。4.2.1聚合分析示例：使用SUM和AVG函数--假设我们想要计算所有订单的总销售额和平均销售额

SELECTSUM(price*quantity)AStotal_sales,AVG(price)ASaverage_price

FROMsales;SUM函数计算总和，而AVG函数计算平均值。4.2.2统计分析示例：使用COUNT和COUNT(DISTINCT)函数--假设我们想要统计不同产品的订单数量和唯一产品数量

SELECTCOUNT(*)AStotal_orders,COUNT(DISTINCTproduct_id)ASunique_products

FROMorders;COUNT函数统计行数，COUNT(DISTINCT)函数统计不同值的数量。4.2.3数据挖掘Presto的窗口函数可以用于复杂的数据挖掘任务，如计算滚动平均或排名。示例：使用ROW_NUMBER函数--假