当前位置：首页 > article >正文

SQL -高阶3

article 2024/11/15 11:00:03

zstarling

字符串拼接与类型转换
最大，最小值，提取日期部分的数值
日期截断

字符串拼接与类型转换

新语法SQL

delete
from public.basiclaw_qr_staff_ac ct
where batch_date = ''' || data_dt || ''' :: date	
and biz_line = ''' || biz_line || ''';

详解
该 SQL 语句使用 DELETE 去删除 public.basiclaw_qr_staff_acct 表中符合特定条件的记录。具体条件为：

batch_date 等于一个值，这个值由 data_dt 变量与两个空白单引构成，即 ‘’ || data_dt || ‘’。
biz_line 等于另一个值，这个值由 biz_line 变量和两个空白单引号构成，即 ‘’ biz_line || ‘’。

其中，|| 用于连接两个字符串。空白单引号 ‘’ 表示一个空字符串，此处使用两个单引号是为了在 SQL 语句中表示一个空白单引号。data_dt 和 biz_line 都是在 SQL 语句执行前定义好的变量，其具体值由应用程序或用户提供。

::date 是 PostgreSQL 中的类型转换语法，表示将 batch_date 列的数据类型转换为 date 类型。

因此，这个 SQL 语句的作用是删除 public.basiclaw_qr_staff_acct 表中符合特定条件的记录，这些条件基于 batch_date 和 biz_line 列的取值。其中，data_dt 和 biz_line 是在执行 DELETE要根据具体情况赋值的变量。

详细解释上述语句中的’‘’ || data_dt || ‘’’ :: date中涉及的用法，并3个举例说明此用法

这个 SQL 语句中的 ‘’‘ || data_dt || ’‘’ :: date 部分涉及到字符串拼接类型转换两个用法。具体来说，这个表达式将 data_dt 变量连接在两个空白单引号(红色标记)(两个空白单元格做转义解释也可以是一个单引号 ' )中，并将拼接后的字符串转换 date 类型。下面对这两个用法做进一步解释并给出举例：

字符串拼接（String Concatenation）
在 SQL 中，常见的字符串拼接运算符有 || 和 +。在上述语句中使用的是 || 运算符来将单引号与_dt 变量拼接起来形成一个新的字符串。例如，假设 data_dt ‘2022-01-01’，那么 ‘’’ || data_dt || ‘’’ 的结果就是 ‘2022-01-01’。这个技巧通常用来将某些字符串或变量值嵌入 SQL 语句中，构建出特定的查询条件。
类型转换（Type Casting）
SQL 中的类型转换运算用来将一个数据类型的值转换为另一个数据类型，目的是使数据类型一致以便进行特定的计算或查询。在 SQL 中，类型转换通常使用 :: 运算符来完成。在上述语句中使用的是 ::date 语法，将拼接好的字符串转换为 date 类型。例如，‘’‘2022-01-01’‘’::date 的结果就是 2022-01-01(date 类型)。

示例

在查询中使用字符串拼接功能，构建出特定的查询条件。例如，在查询某个客户下某个产品销售情况时，使用 || 运算符连接客户编号和产品编号来查询特定客户、特定产品的销售记录：

SELECT * FROM sales WHERE customer_id || product_id = 'ACME01-1234';

在金额计算中进行类型转，确保不同数据类型的值能够参与计算并得到正确的结果。例如，计算总销售金额时，可能需要将销售数量和单价转换为相同的数据类型（比如IMAL）以便进行准确的计算：

SELECT qty * price::DECIMAL AS total_sales FROM sales;

在存储过程或函数中，使用字符串拼接和类型转换以便构建出特定的查询或更新语句。例如，在一个函数中，构建查询销售员的销售记录时使用字符串拼接和类型转换构建出特定的 SQL 查询语：

sales_query := 'SELECT * FROM sales WHERE salesperson_id = ''' || sp_id || ''' AND sales_date >= ''' || start_date || '''::date AND sales_date < ''' || end_date || '''::date';
EXECUTE sales_query INTO sales_data;

问题

为什么连接内容中左右存在空格，是书写规范吗，会影响输出结果吗？
‘’ 是新增单引号方便形成SQL所需的查询格式’xx’吗？

最大，最小值，提取日期部分的数值

新语法SQL

sum(date_part(''day''),least(t.leave_date, t.batch_date) -greatest(t.arrive_date,date_trunc( ''month'',t.batch_date))+1) over(partition by t.staff_no) as duty_days,--在岗天数

在上述SQL中，有几个用到的语法，其中最主要的是sum()和date_part()函数、least()和greatest()函数、over()和partition by关键字，下面对这些语法做详细解释并给出三个示例：

sum()和date_part()函数
sum()函数是一个聚合函数，用于计算指定列的总和。date_part()是一个日期函数，用于提取日期部分的数值，可以有效地处理日期数据。在本SQL语句中，sum(date_part('day', expression))用于计算参数表达式中日期部分的总和。例如，sum(date_part('day', '2022-01-31' - '2022-01-01'))将返回30，表示两个日期之间的天数。
least()和greatest()函数
least和greatest()是用于比较多个数值或日期的函数。least()函数返回其中最小的值，greatest()函数返回其中最大的值。在本SQL语句中，这两个函数用于计算输入的两个起始日期中较早的日期和两个结束日期中较晚的日期。例如，least(‘2022-01-01’, ‘2022-01-31’)将返回'2022-01-01'，而greatest(‘2022-01-01’, ‘2022-01-31’)`将返回’2022-01-31’。
over()和partition by关键字
over()是一个窗口函数，用于在查询的结果中计算聚合、排序、组等操作。partition by是用于指定窗口函数分组的关键字。在这个SQL语句中，它们用于计算每个员工的在岗天数，分别按照员工编号分组计算。例如，sum(...) over (partition by t.staff_no)将对工号相同的每个员工计算在岗天数的总和。如果想了解更详细的窗口函数的使用方法，可以阅读该篇文章SQL-窗口函数详解

示例

使用date_part()计算一组日期中的小时数总和：

SELECT sum(date_part('hour', my_time)) as total_hours FROM my_table;

使用least()和greatest()函数计算一组日期的时间跨度，并返回起始日期和结束日期之间的数据：

SELECT * FROM_table WHERE my_date BETWEEN greatest(start_date, end_date) AND least(start_date, end_date);

使用over()和partition by计算每个销售员的销售总额和百比，分别按照销售员和产品类别分组计算：

 salesperson, product_category, 
       sum(sales) OVER (PARTITION BY salesperson) AS total_sales,
       sum(s) OVER (PARTITION BY salesperson, product_category) / sum(sales) OVER (PARTITION BY salesperson) AS pct_sales
FROM my_sales_table;

日期截断

date_trunc(''month'',t.entry_date)

详解
该句SQL 将entry_date字段的值截断为月份，并返回截断后的结果。

在SQL中，date_trunc函数用于截断日期或时间值，并返回截断后的结果。它接受两个参数：第一个参数是截断的单位（例如：year、month、day、hour等），第二个参数是要截断的日期或时间值。

示例

CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,
  order_date DATE
);

INSERT INTO orders (order_date) VALUES
  ('2023-06-10'),
  ('2023-06-11'),
  ('2023-07-15'),
  ('2023-07-20'),
  ('2023-08-05');

截断到年份：返回值为该年份的第一天，输出格式依旧为年月日的日期格式，而非年份的数值格式。

SELECT date_trunc('year', order_date) FROM orders;

date_trunc
   ------------
   2023-01-01
   2023-01-01
   2023-01-01
   2023-01-01
   2023-01-01

截断到月份：返回值为该月份的第一天，输出格式依旧为年月日的日期格式，而非月份的数值格式。

SELECT date_trunc('month', order_date) FROM orders;

date_trunc
   ------------
   2023-06-01
   2023-06-01
   2023-07-01
   2023-07-01
   2023-08-01

截断到日期：返回值为该天的值。若order_date为年月日时间格式，则输出值为年月日时分秒，此时的时分秒取0时0分0秒，如date_trunc2所示。

SELECT date_trunc('day', order_date) FROM orders;

date_trunc1
   ------------
   2023-06-10
   2023-06-11
   2023-07-15
   2023-07-20
   2023-08-05
   
date_trunc2
   ---------------------
   2023-06-10 00:00:00
   2023-06-11 00:00:00
   2023-07-15 00:00:00
   2023-07-20 00:00:00
   2023-08-05 00:00:00

截断到小时：若order_date为年月日时间格式，则输出值为年月日时分秒，此时的分秒取0分0秒。

SELECT date_trunc('hour', order_date) FROM orders;

date_trunc
   ---------------------
   2023-06-10 10:00:00
   2023-06-11 15:00:00
   2023-07-15 09:00:00
   2023-07-20 16:00:00
   2023-08-05 12:00:00