#PostgreSQL LIMIT 和 OFFSET

SELECT column1, column2, ...
FROM table_name
LIMIT number;

-- 获取前 10 个客户
SELECT * FROM customers
LIMIT 10;

-- 获取薪资最高的 5 名员工
SELECT employee_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

-- 获取 100 条记录用于测试
SELECT * FROM large_table
LIMIT 100;

-- 获取随机样本
SELECT * FROM products
ORDER BY RANDOM()
LIMIT 20;

SELECT column1, column2, ...
FROM table_name
LIMIT number OFFSET number;

-- 跳过前 10 行，返回接下来的 10 行
SELECT * FROM customers
LIMIT 10 OFFSET 10;

-- 获取第 3 页（第 21-30 行）
SELECT * FROM products
ORDER BY product_id
LIMIT 10 OFFSET 20;

-- 第 1 页（第 1-10 行）
SELECT * FROM products
ORDER BY product_id
LIMIT 10 OFFSET 0;

-- 第 2 页（第 11-20 行）
SELECT * FROM products
ORDER BY product_id
LIMIT 10 OFFSET 10;

-- 第 3 页（第 21-30 行）
SELECT * FROM products
ORDER BY product_id
LIMIT 10 OFFSET 20;

-- 根据页码计算偏移量
-- page_size = 20, page_number = 3
SELECT * FROM orders
ORDER BY order_date DESC
LIMIT 20 OFFSET (3 - 1) * 20;  -- OFFSET 40

-- 获取分页结果和总数
WITH paginated_data AS (
    SELECT *,
           COUNT(*) OVER() as total_count
    FROM products
    WHERE category = 'Electronics'
    ORDER BY price DESC
)
SELECT *
FROM paginated_data
LIMIT 10 OFFSET 0;

-- 获取价格最高的 10 个产品
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 10;

-- 获取最近的 5 个订单
SELECT order_id, order_date, total_amount
FROM orders
ORDER BY order_date DESC
LIMIT 5;

-- 获取最早注册的 10 个客户
SELECT customer_name, registration_date
FROM customers
ORDER BY registration_date ASC
LIMIT 10;

-- 获取每个类别的前 3 个产品
SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) as rn
    FROM products
) ranked
WHERE rn <= 3;

-- 获取订单最多的客户（前 10 名）
SELECT c.customer_id, c.customer_name, COUNT(*) as order_count
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY order_count DESC
LIMIT 10;

-- 获取 20 个唯一的产品类别
SELECT DISTINCT category
FROM products
ORDER BY category
LIMIT 20;

-- 返回所有行
SELECT * FROM customers
LIMIT ALL;

-- 在动态查询中很有用，其中限制可能是可选的
SELECT * FROM customers
LIMIT CASE WHEN @apply_limit THEN 10 ELSE NULL END;

-- 创建索引以提高性能
CREATE INDEX idx_orders_date ON orders(order_date DESC);

-- 查询将更快
SELECT * FROM orders
ORDER BY order_date DESC
LIMIT 10;

-- 慢：大偏移量扫描许多行
SELECT * FROM large_table
ORDER BY id
LIMIT 10 OFFSET 1000000;

-- 更好：使用键集分页
SELECT * FROM large_table
WHERE id > 1000000
ORDER BY id
LIMIT 10;

-- 第一页
SELECT * FROM orders
WHERE order_date >= '2024-01-01'
ORDER BY order_date DESC, order_id DESC
LIMIT 10;

-- 下一页（使用上一页的最后一个 order_id）
SELECT * FROM orders
WHERE order_date >= '2024-01-01'
  AND (order_date, order_id) < ('2024-01-15', 12345)
ORDER BY order_date DESC, order_id DESC
LIMIT 10;

-- 按收入排名前 10 的客户
SELECT customer_id,
       customer_name,
       SUM(order_amount) as total_revenue
FROM orders
JOIN customers USING (customer_id)
GROUP BY customer_id, customer_name
ORDER BY total_revenue DESC
LIMIT 10;

-- 获取最近的 50 条日志条目
SELECT * FROM application_logs
ORDER BY created_at DESC
LIMIT 50;

-- 获取随机 100 个产品用于 A/B 测试
SELECT * FROM products
WHERE status = 'active'
ORDER BY RANDOM()
LIMIT 100;

-- 获取前 5 个客户及其最新订单
SELECT DISTINCT ON (c.customer_id)
       c.customer_id,
       c.customer_name,
       o.order_id,
       o.order_date,
       o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_date DESC
LIMIT 5;

-- 获取销量前 10 的产品
SELECT *
FROM products
WHERE product_id IN (
    SELECT product_id
    FROM order_items
    GROUP BY product_id
    ORDER BY SUM(quantity) DESC
    LIMIT 10
);

-- 仪表板：本月销量前 5 的产品
SELECT p.product_name,
       SUM(oi.quantity) as units_sold,
       SUM(oi.quantity * oi.price) as revenue
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_date >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY p.product_id, p.product_name
ORDER BY revenue DESC
LIMIT 5;

-- 用户排行榜：按积分排名前 20
SELECT user_id,
       username,
       total_points,
       RANK() OVER (ORDER BY total_points DESC) as rank
FROM users
WHERE status = 'active'
ORDER BY total_points DESC
LIMIT 20;

-- 预览导入数据的前 10 行
SELECT * FROM imported_data
ORDER BY import_timestamp DESC
LIMIT 10;

-- LIMIT 语法
SELECT * FROM customers
LIMIT 10 OFFSET 5;

-- FETCH 语法（SQL 标准）
SELECT * FROM customers
OFFSET 5 ROWS
FETCH FIRST 10 ROWS ONLY;

-- FETCH NEXT 等同于 FETCH FIRST
SELECT * FROM customers
OFFSET 5 ROWS
FETCH NEXT 10 ROWS ONLY;

-- 不好：结果不可预测
SELECT * FROM products
LIMIT 10;

-- 好：结果一致
SELECT * FROM products
ORDER BY product_id
LIMIT 10;

-- 低效
SELECT * FROM orders
WHERE customer_id = 123
LIMIT 1;

-- 更好：使用 EXISTS
SELECT EXISTS (
    SELECT 1 FROM orders
    WHERE customer_id = 123
);

-- 大偏移量时很慢
SELECT * FROM orders
ORDER BY order_date
LIMIT 10 OFFSET 100000;

-- 更好：使用 WHERE 子句
SELECT * FROM orders
WHERE order_date < '2023-01-01'
ORDER BY order_date DESC
LIMIT 10;