Oracle常用函数(二）

1.在Oracle中，将一行数据转为一列，其对应的字段名为另一列。

有名为"table_a"的表，表结构如下：

Field_A	Field_B	Field_C
Value_1	Value_2	Value_3

SELECT FieldName,Value
FROM (
    SELECT Field_A,Field_B,Field_C
    FROM table_a
)
UNPIVOT (
   Value FOR FieldName IN (Field_A,Field_B,Field_C)
)

2.在Oracle中，使用CASE WHEN语句与SUM函数结合实现条件求和，或者与COUNT()函数结合实现条件计数,与AVG()函数结合，与MAX()函数结合，与MIN函数结合。

SELECT 
  category,
  SUM(CASE WHEN condition THEN value ELSE 0 END) AS sum_value
FROM table_name
GROUP BY category

SELECT 
  column1,
  COUNT(CASE WHEN condition THEN column2 END) AS count_value
FROM table_name
GROUP BY column1

SELECT 
  category,
  AVG(CASE WHEN condition THEN value ELSE null END) AS avg_value
FROM table_name
GROUP BY category

SELECT 
  category,
  MAX(CASE WHEN condition THEN value ELSE null END) AS max_value
FROM table_name
GROUP BY category

SELECT 
  category,
  MIN(CASE WHEN condition THEN value ELSE null END) AS min_value
FROM table_name
GROUP BY category

3.在 Oracle 中，可以使用 PIVOT 或者条件聚合函数（CASE WHEN）来实现行转列的操作。

(1)使用PIVOT

SELECT *
FROM (
  SELECT category, value
  FROM table_name
)
PIVOT (
  MAX(value)
  FOR category IN ('A', 'B', 'C')
)

(2)使用条件聚合函数（CASE WHEN）

SELECT
  MAX(CASE WHEN category = 'A' THEN value END) AS A,
  MAX(CASE WHEN category = 'B' THEN value END) AS B,
  MAX(CASE WHEN category = 'C' THEN value END) AS C
FROM table_name

4.在Oracle中，使用SUM(column3) OVER (PARTITION BY column1 ORDER BY column2)实现计算分组内每行数据的累积求和值。创建表名为sales的表。

column1	column2	column3
A	1	10
A	2	20
A	3	30
B	1	5
B	2	15
B	3	25

使用如下SQL，将得到相应的结果。

SELECT column1, column2, column3,
       SUM(column3) OVER (PARTITION BY column1 ORDER BY column2) AS cumulative_sum
FROM sales

column1	column2	column3	cumulative_sum
A	1	10	10
A	2	20	30
A	3	30	60
B	1	5	5
B	2	15	20
B	3	25	45

5.在Oracle中，使用SUM(column2) OVER (ORDER BY column1) 实现计算每行数据的累积求和值。

SELECT column1, column2, 
       SUM(column2) OVER (ORDER BY column1) AS cumulative_sum
FROM sales

6.在Oracle中，使用ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2)实现组内排序。

SELECT column1, column2,
       ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2) AS row_num
FROM sales