PostgreSQL: PostgreSQL内置函数详解

SELECT
  unit_price,
  ROUND(unit_price, 0) AS rounded_0,
  ROUND(unit_price, 2) AS rounded_2
FROM products
WHERE category = 'Electronics';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT
  total_amount,
  CEIL(total_amount) AS ceil_value,
  FLOOR(total_amount) AS floor_value,
  total_amount - FLOOR(total_amount) AS decimal_part
FROM orders
WHERE order_status = 'completed';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT
  order_id,
  total_amount,
  MOD(order_id, 10) AS partition_key,
  total_amount * POWER(1.08, 1) AS with_annual_tax
FROM orders
WHERE order_status = 'completed';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT
  unit_price,
  SQRT(unit_price) AS price_sqrt,
  POWER(unit_price, 1.0 / 3) AS price_cbrt
FROM products
WHERE unit_price > 0
ORDER BY unit_price DESC;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT
  product_name,
  LOWER(product_name) AS lower_name,
  UPPER(product_name) AS upper_name,
  INITCAP(LOWER(product_name)) AS title_name
FROM products
WHERE is_active = true;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT
  product_name,
  CONCAT(product_name, ' - $', unit_price) AS safe_concat,
  product_name || ' - $' || unit_price AS unsafe_concat,
  CONCAT_WS(' | ', product_name, category, unit_price::TEXT) AS ws_concat
FROM products;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT
  product_name,
  SPLIT_PART(category_path, '/', 1) AS top_category,
  SPLIT_PART(category_path, '/', 2) AS sub_category
FROM products
WHERE category_path LIKE '%/%';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT
  TRIM(product_name) AS clean_name,
  REPLACE(product_name, '  ', ' ') AS no_double_spaces
FROM products
WHERE is_active = true;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT
  LPAD(product_id::TEXT, 8, '0') AS formatted_id,
  product_name,
  unit_price
FROM products
ORDER BY product_id;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT
  CURRENT_DATE AS today,
  CURRENT_TIMESTAMP AS now_ts,
  NOW() AS now_func,
  LOCALTIMESTAMP AS local_ts;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT
  order_date,
  order_date + 7 AS due_date,
  CURRENT_DATE - order_date AS days_ago,
  AGE(CURRENT_DATE, order_date) AS age_since_order
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '90 days';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT
  order_date,
  EXTRACT(YEAR FROM order_date) AS order_year,
  EXTRACT(MONTH FROM order_date) AS order_month,
  EXTRACT(QUARTER FROM order_date) AS order_quarter,
  EXTRACT(DOW FROM order_date) AS weekday
FROM orders
WHERE order_status = 'completed';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT
  DATE_TRUNC('month', order_date) AS month_start,
  COUNT(*) AS order_count,
  SUM(total_amount) AS monthly_revenue
FROM orders
WHERE order_status = 'completed'
  AND order_date >= DATE_TRUNC('year', CURRENT_DATE)
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month_start;

 count 
-------
     5
(1 row)

SELECT
  order_id,
  TO_CHAR(order_date, 'YYYY-Month-DD') AS formatted_date,
  TO_CHAR(order_date, 'YYYY-MM-DD HH24:MI:SS') AS full_datetime,
  TO_CHAR(total_amount, 'FM$999,999.00') AS formatted_amount
FROM orders
WHERE order_status = 'completed';

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT
  TO_DATE('2025-06-15', 'YYYY-MM-DD') AS parsed_date,
  TO_DATE('15/06/2025', 'DD/MM/YYYY') AS eu_date,
  TO_DATE('Jun 15 2025', 'Mon DD YYYY') AS us_date;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT
  product_name,
  unit_price,
  discount_rate,
  COALESCE(discount_rate, 0) AS safe_discount,
  unit_price * (1 - COALESCE(discount_rate, 0)) AS net_price
FROM products;

 count 
-------
     5
(1 row)

SELECT
  product_name,
  unit_price,
  total_sold,
  unit_price / NULLIF(total_sold, 0) AS revenue_per_unit
FROM products
WHERE is_active = true;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

SELECT
  product_name,
  unit_price,
  COALESCE(discount_rate, 0) AS discount,
  unit_price * (1 - GREATEST(COALESCE(discount_rate, 0), 0.5)) AS capped_net_price,
  LEAST(unit_price, 999.99) AS price_cap
FROM products
WHERE is_active = true;

 count 
-------
     5
(1 row)

INSERT INTO orders (order_id, customer_id, total_amount, order_date)
VALUES (NEXTVAL('order_id_seq'), 1001, 299.99, CURRENT_DATE);

INSERT 0 1

SELECT
  NEXTVAL('report_seq') AS report_number,
  CURRVAL('report_seq') AS current_seq;

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- Reset sequence to start from 1 again
SELECT SETVAL('order_id_seq', 1, false);

-- Set sequence to a specific high value (e.g., after data migration)
SELECT SETVAL('order_id_seq', (SELECT MAX(order_id) FROM orders));

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

-- Step 1: Monthly revenue report with date formatting
SELECT
  TO_CHAR(DATE_TRUNC('month', order_date), 'YYYY-Month') AS report_month,
  COUNT(*) AS order_count,
  SUM(total_amount)::NUMERIC(12,2) AS gross_revenue,
  SUM(total_amount * 0.08)::NUMERIC(12,2) AS tax_collected,
  SUM(total_amount * 1.08)::NUMERIC(12,2) AS total_with_tax
FROM orders
WHERE order_status = 'completed'
  AND order_date >= DATE_TRUNC('year', CURRENT_DATE)
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY report_month;

-- Step 2: Product summary with string formatting and NULL handling
SELECT
  LPAD(p.product_id::TEXT, 6, '0') AS product_code,
  INITCAP(LOWER(p.product_name)) AS display_name,
  CONCAT('$', ROUND(p.unit_price, 2)) AS price_label,
  COALESCE(p.discount_rate, 0) AS discount,
  ROUND(p.unit_price * (1 - COALESCE(p.discount_rate, 0)), 2) AS net_price,
  LEAST(ROUND(p.unit_price * (1 - COALESCE(p.discount_rate, 0)), 2), 999.99) AS capped_price
FROM products p
WHERE p.is_active = true
  AND p.unit_price > NULLIF(p.cost_price, 0)
ORDER BY net_price DESC;

-- Step 3: Customer order summary with age calculation
SELECT
  CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
  COUNT(o.order_id) AS total_orders,
  SUM(o.total_amount)::NUMERIC(12,2) AS lifetime_value,
  AGE(MAX(o.order_date), MIN(o.order_date)) AS customer_span,
  EXTRACT(YEAR FROM AGE(CURRENT_DATE, c.register_date)) AS years_since_join
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_status = 'completed'
GROUP BY c.customer_id, c.first_name, c.last_name, c.register_date
ORDER BY lifetime_value DESC
FETCH FIRST 25 ROWS ONLY;