主流开发语言核心语法示例与代码高亮测试指南

对于开发者而言，快速识别和理解不同编程语言的语法结构是一项基本技能。本文旨在提供一个集中的参考，展示包括Python、JavaScript、Java、C#、Go、Rust等在内的13种常见开发语言的基础语法示例。每个示例至少包含5行代码，涵盖了变量定义、控制结构、函数/方法声明等核心概念，非常适合用于测试代码编辑器的语法高亮功能，同时也为开发者提供了一份简洁的跨语言语法速查手册。

1. Python - 简洁高效的脚本语言

Python 示例：

# Python 示例：函数、列表推导式和条件判断
def calculate_fibonacci(n: int) -> list:
    """生成斐波那契数列"""
    if n <= 0:
        return []
    sequence = [0, 1]
    while len(sequence) < n:
        sequence.append(sequence[-1] + sequence[-2])
    return sequence[:n]

# 使用列表推导式生成平方数
squares = [x**2 for x in range(10) if x % 2 == 0]
print(f"斐波那契数列：{calculate_fibonacci(8)}")
print(f"偶数平方列表：{squares}")

2. JavaScript - 动态的Web脚本语言

JavaScript 示例：

// JavaScript 示例：箭头函数、异步操作和ES6模块
const fetchUserData = async (userId) => {
    try {
        const response = await fetch(`/api/users/${userId}`);
        if (!response.ok) throw new Error('Network response was not ok');
        const data = await response.json();
        return data;
    } catch (error) {
        console.error('Fetch error:', error);
        return null;
    }
};

// 数组的高阶函数使用
const numbers = [1, 2, 3, 4, 5];
const doubled = numbers.map(num => num * 2);
console.log(`原始数组：${numbers}，加倍后：${doubled}`);

3. TypeScript - 类型安全的JavaScript超集

TypeScript 示例：

// TypeScript 示例：接口、泛型和类型注解
interface ApiResponse {
    status: number;
    data: T;
    message: string;
}

const processResponse = (response: ApiResponse): T | null => {
    if (response.status >= 200 && response.status < 300) {
        console.log(`Success: ${response.message}`);
        return response.data;
    }
    console.error(`Error ${response.status}: ${response.message}`);
    return null;
};

// 使用定义的接口
const userResponse: ApiResponse<{ id: number; name: string }> = {
    status: 200,
    data: { id: 1, name: 'Alice' },
    message: 'User found'
};
const userData = processResponse(userResponse);

4. Java - 面向对象的跨平台语言

Java 示例：

// Java 示例：类定义、泛型集合和流式API
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

public class Main {
    public static void main(String[] args) {
        // 使用泛型集合
        List names = new ArrayList<>();
        names.add("John");
        names.add("Jane");
        names.add("Doe");

        // 使用Stream API进行过滤和转换
        List filteredNames = names.stream()
                .filter(name -> name.startsWith("J"))
                .map(String::toUpperCase)
                .collect(Collectors.toList());

        System.out.println("Filtered names: " + filteredNames);
        System.out.println("Total count: " + names.size());
    }
}

5. C# - 微软的现代多范式语言

C# 示例：

// C# 示例：属性、LINQ查询和异步方法
using System;
using System.Collections.Generic;
using System.Linq;

public class Program
{
    public string Name { get; set; } // 自动属性

    public static async Task Main(string[] args)
    {
        // 集合初始化器
        var numbers = new List { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };

        // LINQ查询表达式
        var evenSquares = from num in numbers
                          where num % 2 == 0
                          select num * num;

        Console.WriteLine($"Even squares: {string.Join(", ", evenSquares)}");

        // 异步任务示例
        await ProcessDataAsync();
    }

    private static async Task ProcessDataAsync()
    {
        await Task.Delay(1000);
        Console.WriteLine("Async processing completed.");
    }
}

6. Go - 简洁的并发编程语言

Go 示例：

// Go 示例：协程、通道和结构体方法
package main

import (
    "fmt"
    "time"
)

// 定义结构体
type Worker struct {
    ID   int
    Name string
}

// 结构体方法
func (w Worker) PerformTask(task string) {
    fmt.Printf("Worker %d (%s) is processing: %s\n", w.ID, w.Name, task)
}

func main() {
    // 使用通道进行协程间通信
    taskChan := make(chan string, 3)
    doneChan := make(chan bool)

    // 启动工作协程
    go func() {
        for task := range taskChan {
            worker := Worker{ID: 1, Name: "GoRoutine"}
            worker.PerformTask(task)
            time.Sleep(500 * time.Millisecond)
        }
        doneChan <- true
    }()

    // 发送任务
    tasks := []string{"Task A", "Task B", "Task C"}
    for _, task := range tasks {
        taskChan <- task
    }
    close(taskChan)

    <-doneChan
    fmt.Println("All tasks completed.")
}

7. Rust - 内存安全的系统编程语言

Rust 示例：

// Rust 示例：所有权、模式匹配和Result处理
use std::fs::File;
use std::io::{self, Read};

fn read_file_contents(path: &str) -> Result {
    let mut file = File::open(path)?;
    let mut contents = String::new();
    file.read_to_string(&mut contents)?;
    Ok(contents)
}

fn process_data(data: &str) -> Option {
    match data.len() {
        0 => None,
        len if len > 100 => Some(100), // 使用匹配守卫
        len => Some(len),
    }
}

fn main() {
    let filename = "data.txt";
    let result = read_file_contents(filename);

    match result {
        Ok(content) => {
            println!("File read successfully.");
            if let Some(processed_len) = process_data(&content) {
                println!("Processed length: {}", processed_len);
            }
        }
        Err(e) => println!("Error reading file: {}", e),
    }

    // 向量和迭代器
    let numbers = vec![10, 20, 30, 40, 50];
    let sum: i32 = numbers.iter().sum();
    println!("Sum of numbers: {}", sum);
}

8. C++ - 高性能的系统级语言

C++ 示例：

// C++ 示例：智能指针、模板和范围for循环
#include 
#include 
#include 
#include 

template
void print_vector(const std::vector& vec) {
    std::cout << "Vector elements: ";
    for (const auto& element : vec) { // 范围for循环
        std::cout << element << " ";
    }
    std::cout << std::endl;
}

int main() {
    // 使用智能指针管理动态内存
    auto numbers = std::make_unique>();
    numbers->push_back(5);
    numbers->push_back(3);
    numbers->push_back(8);
    numbers->push_back(1);

    // 使用算法库排序
    std::sort(numbers->begin(), numbers->end());
    print_vector(*numbers);

    // Lambda表达式
    auto is_even = [](int n) { return n % 2 == 0; };
    int count = std::count_if(numbers->begin(), numbers->end(), is_even);
    std::cout << "Count of even numbers: " << count << std::endl;

    return 0;
}

9. PHP - 广泛使用的服务器端脚本语言

PHP 示例：

// PHP 示例：类、数组操作和命名空间
namespace App\Services;

class UserService {
    private $users = [];

    public function __construct(array $initialUsers = []) {
        $this->users = $initialUsers;
    }

    public function addUser(string $name, int $age): void {
        $this->users[] = ['name' => $name, 'age' => $age];
    }

    public function getAdultUsers(): array {
        return array_filter($this->users, function($user) {
            return $user['age'] >= 18;
        });
    }

    public function getUserNames(): array {
        return array_column($this->users, 'name');
    }
}

// 使用示例
$service = new UserService([['name' => 'John', 'age' => 25]]);
$service->addUser('Jane', 17);
$adults = $service->getAdultUsers();
echo "Adult users: " . count($adults) . "\n";
print_r($service->getUserNames());

10. Ruby - 优雅灵活的面向对象语言

Ruby 示例：

# Ruby 示例：类、块和符号
class Calculator
  attr_accessor :result

  def initialize
    @result = 0
  end

  def add(value)
    @result += value
    self # 允许链式调用
  end

  def multiply(value)
    @result *= value
    self
  end

  def calculate(&block)
    instance_eval(&block) if block_given?
    @result
  end
end

# 使用块进行计算
calc = Calculator.new
total = calc.calculate do
  add 10
  multiply 3
  add 5
end

puts "Calculation result: #{total}"

# 数组的map和select方法
numbers = [1, 2, 3, 4, 5]
squared_evens = numbers.select { |n| n.even? }.map { |n| n**2 }
puts "Squared even numbers: #{squared_evens}"

11. Swift - Apple的现代编程语言

Swift 示例：

// Swift 示例：可选类型、协议和闭包
import Foundation

protocol Displayable {
    var description: String { get }
}

struct Product: Displayable {
    let id: UUID
    var name: String
    var price: Double

    // 计算属性
    var description: String {
        return "\(name) - $\(price)"
    }

    // 方法
    mutating func applyDiscount(percentage: Double) {
        guard percentage > 0 && percentage <= 50 else {
            print("Invalid discount percentage.")
            return
        }
        price *= (1 - percentage / 100)
    }
}

// 使用高阶函数处理集合
let products = [
    Product(id: UUID(), name: "Laptop", price: 999.99),
    Product(id: UUID(), name: "Mouse", price: 25.50),
    Product(id: UUID(), name: "Keyboard", price: 75.00)
]

let expensiveProducts = products.filter { $0.price > 50 }
                                 .sorted { $0.price > $1.price }

print("Expensive products:")
for product in expensiveProducts {
    print(product.description)
}

// 使用可选绑定
if let firstProduct = products.first {
    print("First product: \(firstProduct.name)")
}

12. Shell (Bash) - 强大的命令行脚本语言

Shell (Bash) 示例：

#!/bin/bash
# Shell脚本示例：函数、条件判断和循环

# 定义函数
backup_files() {
    local source_dir=$1
    local backup_dir=$2
    local timestamp=$(date +"%Y%m%d_%H%M%S")

    if [[ ! -d "$source_dir" ]]; then
        echo "错误：源目录 '$source_dir' 不存在."
        return 1
    fi

    mkdir -p "$backup_dir"
    echo "开始备份 $source_dir 到 $backup_dir/backup_$timestamp.tar.gz"

    # 创建压缩备份
    tar -czf "$backup_dir/backup_$timestamp.tar.gz" -C "$(dirname "$source_dir")" "$(basename "$source_dir")"

    if [[ $? -eq 0 ]]; then
        echo "备份成功完成."
        return 0
    else
        echo "备份过程中出现错误."
        return 1
    fi
}

# 主脚本逻辑
readonly LOG_FILE="/var/log/myapp.log"

# 检查并创建日志文件
if [[ ! -f "$LOG_FILE" ]]; then
    touch "$LOG_FILE"
    echo "$(date): 创建新的日志文件。" >> "$LOG_FILE"
fi

# 循环处理文件
for file in /tmp/*.tmp; do
    if [[ -f "$file" ]]; then
        echo "处理文件: $file" >> "$LOG_FILE"
        # 实际处理逻辑...
    fi
done

# 调用函数
backup_files "/home/user/documents" "/backup"

# 退出状态检查
if [[ $? -eq 0 ]]; then
    echo "脚本执行成功。"
    exit 0
else
    echo "脚本执行失败。"
    exit 1
fi

13. SQL - 结构化查询语言

SQL 示例：

-- =====================================================
-- SQL 示例：复杂查询、事务、窗口函数、存储过程、触发器
-- 演示特性：CTE、聚合、分区、索引、外键约束
-- =====================================================

-- -------------------- 表结构定义 --------------------

-- 用户表
CREATE TABLE users (
    id          SERIAL PRIMARY KEY,
    username    VARCHAR(50) NOT NULL UNIQUE,
    email       VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name   VARCHAR(100),
    avatar_url  TEXT,
    role        VARCHAR(20) DEFAULT 'user' CHECK (role IN ('admin', 'moderator', 'user', 'guest')),
    is_active   BOOLEAN DEFAULT TRUE,
    last_login  TIMESTAMP WITH TIME ZONE,
    created_at  TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 创建索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_role_active ON users(role, is_active);
CREATE INDEX idx_users_created_at ON users(created_at DESC);

-- 产品分类表
CREATE TABLE categories (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL UNIQUE,
    slug        VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    parent_id   INTEGER REFERENCES categories(id) ON DELETE SET NULL,
    sort_order  INTEGER DEFAULT 0,
    is_active   BOOLEAN DEFAULT TRUE,
    created_at  TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_categories_parent_id ON categories(parent_id);
CREATE INDEX idx_categories_slug ON categories(slug);

-- 产品表
CREATE TABLE products (
    id              SERIAL PRIMARY KEY,
    sku             VARCHAR(50) NOT NULL UNIQUE,
    name            VARCHAR(255) NOT NULL,
    slug            VARCHAR(255) NOT NULL UNIQUE,
    description     TEXT,
    long_description TEXT,
    category_id     INTEGER REFERENCES categories(id) ON DELETE RESTRICT,
    price           DECIMAL(12, 2) NOT NULL CHECK (price >= 0),
    compare_at_price DECIMAL(12, 2) CHECK (compare_at_price >= 0),
    cost            DECIMAL(12, 2) DEFAULT 0,
    quantity        INTEGER NOT NULL DEFAULT 0,
    min_quantity    INTEGER DEFAULT 1,
    max_quantity    INTEGER DEFAULT 999,
    weight_kg       DECIMAL(8, 3),
    is_active       BOOLEAN DEFAULT TRUE,
    is_featured     BOOLEAN DEFAULT FALSE,
    tags            TEXT[], -- PostgreSQL 数组类型
    metadata        JSONB,  -- PostgreSQL JSONB 类型
    created_at      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_products_category_id ON products(category_id);
CREATE INDEX idx_products_sku ON products(sku);
CREATE INDEX idx_products_slug ON products(slug);
CREATE INDEX idx_products_price ON products(price);
CREATE INDEX idx_products_active_featured ON products(is_active, is_featured);
CREATE INDEX idx_products_tags ON products USING GIN(tags);
CREATE INDEX idx_products_metadata ON products USING GIN(metadata);

-- 订单表
CREATE TABLE orders (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_number    VARCHAR(20) NOT NULL UNIQUE,
    user_id         INTEGER NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    status          VARCHAR(20) DEFAULT 'pending' CHECK (status IN (
        'pending', 'confirmed', 'processing', 'shipped', 
        'delivered', 'cancelled', 'refunded'
    )),
    total_amount    DECIMAL(12, 2) NOT NULL CHECK (total_amount >= 0),
    discount_amount DECIMAL(12, 2) DEFAULT 0,
    shipping_amount DECIMAL(12, 2) DEFAULT 0,
    tax_amount      DECIMAL(12, 2) DEFAULT 0,
    shipping_address JSONB NOT NULL,
    billing_address  JSONB,
    payment_method   VARCHAR(50),
    payment_status   VARCHAR(20) DEFAULT 'pending',
    notes           TEXT,
    placed_at       TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_order_number ON orders(order_number);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_placed_at ON orders(placed_at DESC);
CREATE INDEX idx_orders_shipping_address ON orders USING GIN(shipping_address);

-- 订单明细表
CREATE TABLE order_items (
    id              SERIAL PRIMARY KEY,
    order_id        UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id      INTEGER NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    sku             VARCHAR(50) NOT NULL,
    product_name    VARCHAR(255) NOT NULL,
    quantity        INTEGER NOT NULL CHECK (quantity > 0),
    unit_price      DECIMAL(12, 2) NOT NULL CHECK (unit_price >= 0),
    discount_amount DECIMAL(12, 2) DEFAULT 0,
    total_price     DECIMAL(12, 2) GENERATED ALWAYS AS 
        (quantity * unit_price - discount_amount) STORED,
    metadata        JSONB,
    created_at      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

-- 产品评论表
CREATE TABLE product_reviews (
    id          SERIAL PRIMARY KEY,
    product_id  INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    rating      INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
    title       VARCHAR(255),
    content     TEXT,
    is_verified_purchase BOOLEAN DEFAULT FALSE,
    helpful_count INTEGER DEFAULT 0,
    created_at  TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(product_id, user_id)
);

CREATE INDEX idx_product_reviews_product_id ON product_reviews(product_id);
CREATE INDEX idx_product_reviews_user_id ON product_reviews(user_id);
CREATE INDEX idx_product_reviews_rating ON product_reviews(rating);

-- -------------------- 触发器函数 --------------------

-- 自动更新 updated_at 字段
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_products_updated_at
    BEFORE UPDATE ON products
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_orders_updated_at
    BEFORE UPDATE ON orders
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_product_reviews_updated_at
    BEFORE UPDATE ON product_reviews
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- 生成订单号触发器
CREATE OR REPLACE FUNCTION generate_order_number()
RETURNS TRIGGER AS $$
BEGIN
    NEW.order_number := 'ORD-' || TO_CHAR(CURRENT_DATE, 'YYYYMMDD') || '-' || 
        LPAD(CAST(nextval('order_number_seq') AS TEXT), 6, '0');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE SEQUENCE IF NOT EXISTS order_number_seq START 1;

CREATE TRIGGER trg_generate_order_number
    BEFORE INSERT ON orders
    FOR EACH ROW
    WHEN (NEW.order_number IS NULL)
    EXECUTE FUNCTION generate_order_number();

-- 库存更新触发器
CREATE OR REPLACE FUNCTION update_product_quantity()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE products 
    SET quantity = quantity - NEW.quantity
    WHERE id = NEW.product_id;
    
    -- 检查库存是否为负
    IF (SELECT quantity FROM products WHERE id = NEW.product_id) < 0 THEN
        RAISE EXCEPTION 'Insufficient inventory for product_id: %', NEW.product_id;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_inventory
    AFTER INSERT ON order_items
    FOR EACH ROW EXECUTE FUNCTION update_product_quantity();

-- -------------------- 视图 --------------------

-- 产品销售统计视图
CREATE OR REPLACE VIEW product_sales_summary AS
WITH sales_data AS (
    SELECT 
        p.id,
        p.sku,
        p.name,
        p.category_id,
        c.name AS category_name,
        COUNT(DISTINCT o.id) AS order_count,
        COALESCE(SUM(oi.quantity), 0) AS total_quantity_sold,
        COALESCE(SUM(oi.total_price), 0) AS total_revenue,
        AVG(oi.unit_price) AS avg_selling_price,
        COALESCE(AVG(pr.rating), 0) AS avg_rating,
        COUNT(DISTINCT pr.id) AS review_count
    FROM products p
    LEFT JOIN categories c ON p.category_id = c.id
    LEFT JOIN order_items oi ON p.id = oi.product_id
    LEFT JOIN orders o ON oi.order_id = o.id 
        AND o.status IN ('delivered', 'shipped')
    LEFT JOIN product_reviews pr ON p.id = pr.product_id
    GROUP BY p.id, p.sku, p.name, p.category_id, c.name
)
SELECT 
    *,
    CASE 
        WHEN total_revenue > 10000 THEN 'High Performer'
        WHEN total_revenue > 5000 THEN 'Steady Seller'
        WHEN total_revenue > 1000 THEN 'Moderate'
        WHEN total_revenue > 0 THEN 'Low Volume'
        ELSE 'No Sales'
    END AS performance_tier
FROM sales_data;

-- 用户订单概览视图
CREATE OR REPLACE VIEW user_order_summary AS
SELECT 
    u.id AS user_id,
    u.username,
    u.email,
    u.full_name,
    COUNT(o.id) AS total_orders,
    COUNT(CASE WHEN o.status = 'delivered' THEN 1 END) AS completed_orders,
    COALESCE(SUM(o.total_amount), 0) AS total_spent,
    COALESCE(AVG(o.total_amount), 0) AS avg_order_value,
    MAX(o.placed_at) AS last_order_date,
    EXTRACT(DAY FROM CURRENT_TIMESTAMP - u.created_at) AS days_since_registration
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username, u.email, u.full_name, u.created_at;

-- -------------------- 存储过程 / 函数 --------------------

-- 获取产品推荐（基于购买历史）
CREATE OR REPLACE FUNCTION get_product_recommendations(
    p_user_id INTEGER,
    p_limit INTEGER DEFAULT 10
)
RETURNS TABLE (
    product_id INTEGER,
    sku VARCHAR,
    product_name VARCHAR,
    price DECIMAL,
    category_name VARCHAR,
    avg_rating NUMERIC,
    recommendation_score NUMERIC
) AS $$
BEGIN
    RETURN QUERY
    WITH user_purchased_categories AS (
        SELECT DISTINCT p.category_id
        FROM orders o
        JOIN order_items oi ON o.id = oi.order_id
        JOIN products p ON oi.product_id = p.id
        WHERE o.user_id = p_user_id
          AND o.status IN ('delivered', 'shipped')
    ),
    user_purchased_products AS (
        SELECT DISTINCT oi.product_id
        FROM orders o
        JOIN order_items oi ON o.id = oi.order_id
        WHERE o.user_id = p_user_id
    ),
    category_ranked_products AS (
        SELECT 
            p.id,
            p.sku,
            p.name,
            p.price,
            c.name AS category_name,
            COALESCE(ps.avg_rating, 0) AS avg_rating,
            ps.total_quantity_sold,
            ROW_NUMBER() OVER (
                PARTITION BY p.category_id 
                ORDER BY ps.total_quantity_sold DESC, p.price ASC
            ) AS category_rank
        FROM products p
        JOIN categories c ON p.category_id = c.id
        LEFT JOIN product_sales_summary ps ON p.id = ps.id
        WHERE p.is_active = TRUE
          AND p.id NOT IN (SELECT product_id FROM user_purchased_products)
    )
    SELECT 
        crp.id,
        crp.sku,
        crp.name,
        crp.price,
        crp.category_name,
        ROUND(crp.avg_rating::NUMERIC, 2),
        ROUND(
            (CASE WHEN crp.category_id IN (SELECT category_id FROM user_purchased_categories) 
                  THEN 10 ELSE 0 END) +
            (crp.total_quantity_sold * 0.01) +
            (crp.avg_rating * 2) -
            (crp.category_rank * 0.5),
            2
        ) AS score
    FROM category_ranked_products crp
    WHERE crp.category_rank <= 5
    ORDER BY score DESC
    LIMIT p_limit;
END;
$$ LANGUAGE plpgsql;

-- 计算订单总额的存储过程
CREATE OR REPLACE PROCEDURE calculate_order_totals(
    IN p_order_id UUID,
    OUT p_subtotal DECIMAL,
    OUT p_tax DECIMAL,
    OUT p_total DECIMAL
) AS $$
DECLARE
    v_tax_rate DECIMAL := 0.08; -- 8% 税率
BEGIN
    SELECT COALESCE(SUM(oi.total_price), 0)
    INTO p_subtotal
    FROM order_items oi
    WHERE oi.order_id = p_order_id;
    
    p_tax := ROUND(p_subtotal * v_tax_rate, 2);
    p_total := p_subtotal + p_tax;
    
    UPDATE orders 
    SET total_amount = p_total,
        tax_amount = p_tax,
        updated_at = CURRENT_TIMESTAMP
    WHERE id = p_order_id;
    
    RAISE NOTICE 'Order % totals: Subtotal=%, Tax=%, Total=%', 
        p_order_id, p_subtotal, p_tax, p_total;
END;
$$ LANGUAGE plpgsql;

-- -------------------- 复杂查询示例 --------------------

-- 1. 使用 CTE 和窗口函数进行销售分析
WITH monthly_sales AS (
    SELECT 
        DATE_TRUNC('month', o.placed_at) AS month,
        p.category_id,
        c.name AS category_name,
        COUNT(DISTINCT o.id) AS order_count,
        SUM(oi.quantity) AS units_sold,
        SUM(oi.total_price) AS revenue
    FROM orders o
    JOIN order_items oi ON o.id = oi.order_id
    JOIN products p ON oi.product_id = p.id
    JOIN categories c ON p.category_id = c.id
    WHERE o.status IN ('delivered', 'shipped')
    GROUP BY DATE_TRUNC('month', o.placed_at), p.category_id, c.name
),
category_rankings AS (
    SELECT 
        month,
        category_name,
        revenue,
        units_sold,
        RANK() OVER (PARTITION BY month ORDER BY revenue DESC) AS revenue_rank,
        LAG(revenue) OVER (PARTITION BY category_name ORDER BY month) AS prev_month_revenue,
        ROUND(
            (revenue - LAG(revenue) OVER (PARTITION BY category_name ORDER BY month)) 
            / NULLIF(LAG(revenue) OVER (PARTITION BY category_name ORDER BY month), 0) * 100,
            2
        ) AS growth_percentage
    FROM monthly_sales
)
SELECT 
    TO_CHAR(month, 'YYYY-MM') AS month_display,
    category_name,
    TO_CHAR(revenue, 'FM$999,999,990.00') AS formatted_revenue,
    units_sold,
    revenue_rank,
    TO_CHAR(prev_month_revenue, 'FM$999,999,990.00') AS prev_month_revenue,
    growth_percentage || '%' AS growth
FROM category_rankings
WHERE month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '6 months')
ORDER BY month DESC, revenue_rank;

-- 2. 递归 CTE 查询分类树
WITH RECURSIVE category_tree AS (
    -- 锚点：根分类
    SELECT 
        id,
        name,
        slug,
        parent_id,
        0 AS level,
        name AS path
    FROM categories
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- 递归：子分类
    SELECT 
        c.id,
        c.name,
        c.slug,
        c.parent_id,
        ct.level + 1,
        ct.path || ' > ' || c.name
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT 
    id,
    REPEAT('  ', level) || name AS indented_name,
    level,
    path,
    (SELECT COUNT(*) FROM products WHERE category_id = ct.id) AS product_count
FROM category_tree ct
ORDER BY path;

-- 3. 用户购买行为分析（RFM）
WITH user_rfm AS (
    SELECT 
        u.id AS user_id,
        u.username,
        u.email,
        MAX(o.placed_at) AS last_purchase_date,
        EXTRACT(DAY FROM CURRENT_TIMESTAMP - MAX(o.placed_at)) AS recency_days,
        COUNT(DISTINCT o.id) AS frequency,
        COALESCE(SUM(o.total_amount), 0) AS monetary
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id 
        AND o.status IN ('delivered', 'shipped')
    GROUP BY u.id, u.username, u.email
),
rfm_scores AS (
    SELECT 
        *,
        NTILE(5) OVER (ORDER BY recency_days ASC) AS r_score,
        NTILE(5) OVER (ORDER BY frequency DESC) AS f_score,
        NTILE(5) OVER (ORDER BY monetary DESC) AS m_score
    FROM user_rfm
    WHERE last_purchase_date IS NOT NULL
)
SELECT 
    username,
    email,
    TO_CHAR(last_purchase_date, 'YYYY-MM-DD') AS last_purchase,
    recency_days::INT AS days_since_purchase,
    frequency AS order_count,
    TO_CHAR(monetary, 'FM$999,990.00') AS total_spent,
    r_score,
    f_score,
    m_score,
    CONCAT(r_score, f_score, m_score) AS rfm_cell,
    CASE 
        WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Champions'
        WHEN r_score >= 3 AND f_score >= 3 AND m_score >= 3 THEN 'Loyal'
        WHEN r_score >= 4 AND f_score <= 2 THEN 'New Customers'
        WHEN r_score <= 2 AND f_score >= 4 THEN 'At Risk'
        WHEN r_score <= 2 AND f_score <= 2 AND m_score <= 2 THEN 'Lost'
        ELSE 'Others'
    END AS segment
FROM rfm_scores
ORDER BY monetary DESC
LIMIT 20;

-- 4. 产品库存预警查询
SELECT 
    p.id,
    p.sku,
    p.name,
    p.quantity AS current_stock,
    p.min_quantity,
    COALESCE(ps.total_quantity_sold, 0) AS sold_last_30_days,
    ROUND(COALESCE(ps.total_quantity_sold, 0) / 30.0, 2) AS daily_sales_rate,
    CASE 
        WHEN p.quantity <= 0 THEN 'Out of Stock'
        WHEN p.quantity <= p.min_quantity THEN 'Low Stock - Reorder Now'
        WHEN p.quantity <= p.min_quantity * 2 THEN 'Stock Running Low'
        WHEN p.quantity > p.min_quantity * 5 THEN 'Overstocked'
        ELSE 'Healthy'
    END AS stock_status,
    CASE 
        WHEN COALESCE(ps.total_quantity_sold, 0) > 0 
        THEN ROUND(p.quantity / (COALESCE(ps.total_quantity_sold, 1) / 30.0), 0)
        ELSE 999 
    END AS days_of_inventory_left
FROM products p
LEFT JOIN product_sales_summary ps ON p.id = ps.id
WHERE p.is_active = TRUE
ORDER BY 
    CASE 
        WHEN p.quantity <= p.min_quantity THEN 0
        WHEN p.quantity <= p.min_quantity * 2 THEN 1
        ELSE 2
    END,
    days_of_inventory_left ASC;

-- 5. 使用 JSONB 函数查询
SELECT 
    p.id,
    p.name,
    p.metadata->>'brand' AS brand,
    p.metadata->>'color' AS color,
    p.metadata->'dimensions'->>'width' AS width,
    p.metadata->'dimensions'->>'height' AS height,
    p.metadata->'specifications' AS specifications,
    jsonb_array_length(p.metadata->'images') AS image_count
FROM products p
WHERE p.metadata IS NOT NULL
  AND p.metadata @> '{"brand": "TechCorp"}'
  AND p.metadata->'dimensions'->>'width' IS NOT NULL
ORDER BY (p.metadata->>'release_date')::DATE DESC NULLS LAST;

-- -------------------- 事务示例 --------------------

BEGIN;

-- 创建新订单
INSERT INTO orders (user_id, status, shipping_address, payment_method)
VALUES (
    1,
    'pending',
    '{"street": "123 Main St", "city": "Tech City", "zip": "94105"}'::JSONB,
    'credit_card'
)
RETURNING id INTO v_order_id;

-- 添加订单项（触发器会自动扣减库存）
INSERT INTO order_items (order_id, product_id, sku, product_name, quantity, unit_price)
VALUES 
    (v_order_id, 101, 'SKU-101', 'Wireless Mouse', 2, 29.99),
    (v_order_id, 205, 'SKU-205', 'Mechanical Keyboard', 1, 89.99);

-- 计算订单总额
CALL calculate_order_totals(v_order_id, v_subtotal, v_tax, v_total);

-- 更新订单状态
UPDATE orders SET status = 'confirmed' WHERE id = v_order_id;

COMMIT;

-- -------------------- 聚合与分组查询 --------------------

-- 按小时统计订单量
SELECT 
    EXTRACT(HOUR FROM placed_at) AS hour_of_day,
    COUNT(*) AS order_count,
    ROUND(AVG(total_amount), 2) AS avg_order_value,
    SUM(total_amount) AS total_revenue
FROM orders
WHERE placed_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY EXTRACT(HOUR FROM placed_at)
ORDER BY hour_of_day;

-- 产品标签分析
SELECT 
    UNNEST(tags) AS tag,
    COUNT(*) AS product_count,
    ROUND(AVG(price), 2) AS avg_price,
    SUM(quantity) AS total_inventory
FROM products
WHERE is_active = TRUE
  AND tags IS NOT NULL
GROUP BY tag
HAVING COUNT(*) > 1
ORDER BY product_count DESC;

-- 评论情感分析模拟
SELECT 
    p.id AS product_id,
    p.name AS product_name,
    COUNT(pr.id) AS total_reviews,
    ROUND(AVG(pr.rating), 2) AS avg_rating,
    COUNT(CASE WHEN pr.rating >= 4 THEN 1 END) AS positive_reviews,
    COUNT(CASE WHEN pr.rating <= 2 THEN 1 END) AS negative_reviews,
    ROUND(
        100.0 * COUNT(CASE WHEN pr.rating >= 4 THEN 1 END) / NULLIF(COUNT(pr.id), 0),
        1
    ) AS positive_percentage
FROM products p
LEFT JOIN product_reviews pr ON p.id = pr.product_id
GROUP BY p.id, p.name
HAVING COUNT(pr.id) >= 5
ORDER BY avg_rating DESC, total_reviews DESC;