Presto函数实战指南：从基础到高阶应用

1. Presto函数入门从零开始掌握基础操作第一次接触Presto函数时我完全被它丰富的功能震撼到了。记得当时我需要快速分析一个包含数百万条记录的日志表传统方法需要写复杂的MapReduce作业而Presto仅用几行SQL函数就搞定了。下面我就从最基础的部分带你入门。Presto的函数体系就像一套完整的瑞士军刀包含了数据处理所需的各类工具。最基本的逻辑运算符是每个开发者必须掌握的-- 逻辑与操作两个条件都成立时返回true SELECT 3 3 AND 2 2; -- 返回true -- 逻辑或操作任一条件成立时返回true SELECT 3 1 OR 2 2; -- 返回true -- 逻辑非操作条件不成立时返回true SELECT NOT 2 1; -- 返回true比较函数在实际工作中使用频率极高特别是在数据过滤场景。这里有个实际案例我们需要筛选出年龄在18-35岁之间的用户SELECT user_id, age FROM user_profile WHERE age BETWEEN 18 AND 35; -- 包含边界值处理NULL值是数据清洗中的常见需求。Presto提供了专门的NULL判断函数-- 检查字段是否为NULL SELECT order_id FROM orders WHERE shipping_address IS NULL; -- 检查字段是否非NULL SELECT product_name FROM inventory WHERE stock_count IS NOT NULL;条件表达式能让我们的SQL更加灵活。CASE WHEN就像SQL中的if-else语句我经常用它来做数据分类SELECT user_id, CASE WHEN age 18 THEN 未成年 WHEN age BETWEEN 18 AND 60 THEN 成年 ELSE 老年 END AS age_group FROM users;类型转换是数据预处理的关键步骤。Presto的CAST函数使用起来非常直观-- 将字符串转为日期 SELECT CAST(2023-07-15 AS DATE); -- 尝试转换失败返回NULL SELECT TRY_CAST(ABC AS INTEGER); -- 返回NULL2. 数学与统计函数实战技巧在数据分析领域数学函数是我们的得力助手。记得有一次分析销售数据时我用了大量数学函数来计算各种指标。先看几个基础但实用的例子-- 绝对值计算 SELECT ABS(-15); -- 返回15 -- 四舍五入 SELECT ROUND(4.567, 2); -- 返回4.57 -- 取整函数 SELECT CEIL(3.2); -- 向上取整返回4 SELECT FLOOR(3.8); -- 向下取整返回3幂运算和对数在增长率计算中特别有用-- 计算2的10次方 SELECT POW(2, 10); -- 返回1024 -- 自然对数 SELECT LN(10); -- 返回约2.302585 -- 以10为底的对数 SELECT LOG10(1000); -- 返回3三角函数在位置服务、图像处理等场景很常见。这里有个计算两点间距离的实际案例SELECT SQRT( POW(x2 - x1, 2) POW(y2 - y1, 2) ) AS distance FROM locations;统计函数是数据分析的核心。WIDTH_BUCKET函数可以快速做数据分箱-- 将成绩分为5个等级 SELECT student_id, WIDTH_BUCKET(score, 0, 100, 5) AS grade_level FROM exam_results;随机数生成在数据抽样时非常实用-- 生成0到1之间的随机数 SELECT RAND(); -- 按10%比例随机抽样 SELECT * FROM large_table WHERE RAND() 0.1;3. 字符串处理高级技法字符串处理是ETL过程中最常见的任务之一。Presto提供了丰富的字符串函数我几乎每天都会用到它们。基础的字符串操作包括连接、大小写转换等-- 字符串连接 SELECT CONCAT(Hello, , World); -- 返回Hello World -- 带分隔符的连接 SELECT CONCAT_WS(-, 2023, 07, 15); -- 返回2023-07-15 -- 大小写转换 SELECT LOWER(Presto SQL); -- 返回presto sql SELECT UPPER(Presto SQL); -- 返回PRESTO SQL字符串截取和填充在格式化输出时很有用-- 截取子字符串 SELECT SUBSTR(Presto Functions, 8, 9); -- 返回Functions -- 左侧填充 SELECT LPAD(5, 3, 0); -- 返回005 -- 右侧填充 SELECT RPAD(Hi, 5, !); -- 返回Hi!!!处理空格是数据清洗的常见需求-- 去除前后空格 SELECT TRIM( Presto ); -- 返回Presto -- 只去除左侧空格 SELECT LTRIM( SQL ); -- 返回SQL -- 只去除右侧空格 SELECT RTRIM( Rocks ); -- 返回 Rocks字符串查找和替换操作-- 查找子串位置 SELECT STRPOS(Learn Presto, Pres); -- 返回7 -- 替换字符串 SELECT REPLACE(Presto is fast, fast, powerful); -- 返回Presto is powerful高级字符串分割技术-- 简单分割 SELECT SPLIT(apple,orange,banana, ,); -- 返回[apple,orange,banana] -- 带限制的分割 SELECT SPLIT(2023-07-15, -, 2); -- 返回[2023,07-15] -- 分割为Map SELECT SPLIT_TO_MAP(name:John,age:30, ,, :); -- 返回{name:John,age:30}4. 时间函数与日期处理实战时间数据处理是分析工作中不可或缺的部分。Presto的时间函数让这类操作变得简单高效。获取当前时间是最基础的操作-- 当前日期 SELECT CURRENT_DATE; -- 当前时间戳 SELECT CURRENT_TIMESTAMP; -- 当前时区 SELECT CURRENT_TIMEZONE();日期转换和格式化-- 字符串转日期 SELECT DATE 2023-07-15; -- 时间戳转换 SELECT FROM_UNIXTIME(1689340800); -- 返回2023-07-15 00:00:00.000 -- 日期转UNIX时间戳 SELECT TO_UNIXTIME(DATE 2023-07-15); -- 返回1689340800日期截断在按时间维度聚合时特别有用-- 按小时截断 SELECT DATE_TRUNC(hour, TIMESTAMP 2023-07-15 14:30:45); -- 返回2023-07-15 14:00:00.000 -- 按月截断 SELECT DATE_TRUNC(month, DATE 2023-07-15); -- 返回2023-07-01日期加减计算-- 加3天 SELECT DATE_ADD(day, 3, DATE 2023-07-15); -- 返回2023-07-18 -- 减2个月 SELECT DATE_ADD(month, -2, DATE 2023-07-15); -- 返回2023-05-15日期差值计算-- 计算两个日期相差的天数 SELECT DATE_DIFF(day, DATE 2023-01-01, DATE 2023-07-15); -- 返回195提取日期部分-- 提取年份 SELECT EXTRACT(YEAR FROM CURRENT_DATE); -- 获取月份 SELECT MONTH(CURRENT_DATE); -- 获取季度 SELECT QUARTER(CURRENT_DATE);5. JSON与正则表达式高级应用在现代数据分析中处理半结构化数据变得越来越重要。Presto的JSON函数让这类操作变得轻松。基本的JSON操作-- 检查JSON值类型 SELECT IS_JSON_SCALAR(123); -- 返回true -- 检查数组包含 SELECT JSON_ARRAY_CONTAINS([1,2,3], 2); -- 返回true -- 获取数组长度 SELECT JSON_ARRAY_LENGTH([1,2,3]); -- 返回3JSON路径提取-- 提取JSON字段 SELECT JSON_EXTRACT({name:John,age:30}, $.name); -- 返回John -- 提取数组元素 SELECT JSON_EXTRACT({scores:[85,92,78]}, $.scores[1]); -- 返回92正则表达式是处理复杂文本的利器。我在日志分析中经常使用-- 简单匹配 SELECT REGEXP_LIKE(hello123, \\d); -- 返回true -- 提取匹配内容 SELECT REGEXP_EXTRACT(订单号ORD12345, ORD\\d); -- 返回ORD12345 -- 替换内容 SELECT REGEXP_REPLACE(2023-07-15, (\\d{4})-(\\d{2})-(\\d{2}), $2/$3/$1); -- 返回07/15/2023高级正则操作-- 分割字符串 SELECT REGEXP_SPLIT(apple,banana,orange, ,); -- 返回[apple,banana,orange] -- 提取所有匹配 SELECT REGEXP_EXTRACT_ALL(测试1A,测试2B,测试3C, 测试(\\d)(\\w)); -- 返回[[1,A],[2,B],[3,C]]JSON与正则结合使用的实际案例-- 从JSON日志中提取错误信息 SELECT log_id, REGEXP_EXTRACT( JSON_EXTRACT(log_data, $.message), 错误(.*) ) AS error_message FROM server_logs WHERE JSON_EXTRACT_SCALAR(log_data, $.level) ERROR;6. 聚合函数与高级分析技巧聚合函数是数据分析的核心工具Presto提供了丰富的聚合功能。基本聚合操作-- 计数 SELECT COUNT(*) FROM orders; -- 求和 SELECT SUM(amount) FROM transactions; -- 平均值 SELECT AVG(rating) FROM product_reviews; -- 最大值/最小值 SELECT MAX(temperature), MIN(temperature) FROM weather_data;分组聚合-- 按类别统计 SELECT product_category, COUNT(*) AS product_count, AVG(price) AS avg_price FROM products GROUP BY product_category;DISTINCT去重-- 计算不重复值 SELECT COUNT(DISTINCT user_id) FROM user_actions; -- 多列去重 SELECT DISTINCT department, job_title FROM employees;高级聚合函数-- 近似唯一计数大数据集性能更好 SELECT APPROX_DISTINCT(user_ip) FROM access_logs; -- 百分位数计算 SELECT APPROX_PERCENTILE(response_time, 0.95) FROM api_logs; -- 直方图分箱 SELECT HISTOGRAM(age, 0, 100, 10) FROM users;窗口函数是高级分析的神器-- 计算排名 SELECT product_id, sales, RANK() OVER (ORDER BY sales DESC) AS sales_rank FROM products; -- 移动平均 SELECT date, revenue, AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM daily_sales;7. 性能优化与最佳实践在实际工作中Presto函数的性能优化至关重要。以下是我总结的一些实战经验。函数选择优化-- 使用更高效的函数变体 SELECT APPROX_DISTINCT(user_id) FROM large_table; -- 比COUNT(DISTINCT)更快 -- 避免在WHERE子句中使用函数 -- 不推荐 SELECT * FROM orders WHERE DATE_FORMAT(order_date, %Y-%m) 2023-07; -- 推荐 SELECT * FROM orders WHERE order_date BETWEEN 2023-07-01 AND 2023-07-31;类型处理建议-- 显式类型转换 SELECT CAST(123 AS INTEGER) 1; -- 明确类型转换 -- 避免隐式转换 SELECT 123 1; -- 可能产生意外结果NULL值处理技巧-- 使用COALESCE设置默认值 SELECT product_name, COALESCE(stock_count, 0) AS actual_stock FROM inventory; -- 使用NULLIF避免除零错误 SELECT revenue / NULLIF(visitors, 0) AS rpv FROM metrics;复杂表达式优化-- 将复杂计算拆分为CTE WITH prepared_data AS ( SELECT user_id, DATE_TRUNC(month, login_time) AS month, COUNT(*) AS login_count FROM user_logins GROUP BY 1, 2 ) SELECT month, AVG(login_count) AS avg_logins FROM prepared_data GROUP BY month;函数组合的最佳实践-- 合理嵌套函数 SELECT user_id, SUBSTR( REGEXP_EXTRACT(email, (.)), 1, 3 ) AS name_prefix FROM users; -- 避免过度嵌套 -- 不推荐 SELECT TRIM(SPLIT_PART(REPLACE(LOWER(comment), \n, ), , 1)) FROM reviews; -- 推荐 WITH cleaned_comments AS ( SELECT LOWER(comment) AS lower_comment, REPLACE(comment, \n, ) AS no_newline FROM reviews ) SELECT TRIM(SPLIT_PART(no_newline, , 1)) AS first_word FROM cleaned_comments;