DB-GPT 能力验证（百万级别）

WITH sales_by_category AS (
    SELECT 
        p.category,
        SUM(oi.subtotal) AS total_sales
    FROM 
        orders o
    JOIN 
        order_items oi ON o.order_id = oi.order_id
    JOIN 
        products p ON oi.product_id = p.product_id
    WHERE 
        o.create_time >= CURRENT_DATE - INTERVAL '3 months'
        AND o.order_status > 0 -- 假设订单状态大于0表示有效订单
    GROUP BY 
        p.category
),
total_sales AS (
    SELECT SUM(total_sales) AS grand_total FROM sales_by_category
)
SELECT 
    sc.category,
    sc.total_sales,
    ROUND((sc.total_sales / ts.grand_total * 100), 2) AS percentage
FROM 
    sales_by_category sc,
    total_sales ts
ORDER BY 
    sc.total_sales DESC;

-- 通过分析排名前十的用户的订单数据，统计他们购买的商品类别及其消费金额分布，找出主要购买的商品类别。 
WITH
  top_users AS (
    SELECT
      o.user_id,
      SUM(o.total_amount) AS total_spent
    FROM
      orders o
    GROUP BY
      o.user_id
    ORDER BY
      total_spent DESC
    LIMIT
      10
  ),
  user_category_spending AS (
    SELECT
      tu.user_id,
      p.category,
      SUM(oi.quantity * oi.unit_price) AS category_spent
    FROM
      top_users tu
      JOIN orders o ON tu.user_id = o.user_id
      JOIN order_items oi ON o.order_id = oi.order_id
      JOIN products p ON oi.product_id = p.product_id
    GROUP BY
      tu.user_id,
      p.category
  )
SELECT
  us.category AS 商品类别,
  SUM(us.category_spent) AS 总消费金额
FROM
  user_category_spending us
GROUP BY
  us.category
ORDER BY
  总消费金额 DESC;

-- 通过分析订单的创建时间，可以统计不同时间段的订单量分布情况，从而找到订单量最多的时段和是否存在明显的下单高峰期。可以按小时或日期分组统计数据。 
SELECT
  EXTRACT(
    HOUR
    FROM
      create_time
  ) AS hour_of_day,
  COUNT(order_id) AS order_count
FROM
  orders
GROUP BY
  EXTRACT(
    HOUR
    FROM
      create_time
  )
ORDER BY
  hour_of_day;

-- 除了按小时分析外，还可以从每天的角度查看订单量的变化趋势，进一步确认是否存在某些天的订单量明显更高。 
SELECT
  DATE (create_time) AS order_date,
  COUNT(order_id) AS order_count
FROM
  orders
GROUP BY
  DATE (create_time)
ORDER BY
  order_date;
-- 为了更全面地了解用户下单行为，可以分析一周中每一天的订单量，判断是否有特定星期几订单量较高。 
SELECT
  EXTRACT(
    DOW
    FROM
      create_time
  ) AS day_of_week,
  COUNT(order_id) AS order_count
FROM
  orders
GROUP BY
  EXTRACT(
    DOW
    FROM
      create_time
  )
ORDER BY
  day_of_week;
-- 最后可以结合月份数据，观察全年订单量的分布情况，以识别是否存在季节性高峰。 
SELECT
  EXTRACT(
    MONTH
    FROM
      create_time
  ) AS month_of_year,
  COUNT(order_id) AS order_count
FROM
  orders
GROUP BY
  EXTRACT(
    MONTH
    FROM
      create_time
  )
ORDER BY
  month_of_year;

-- 通过分析订单的创建时间，可以统计不同时间段的订单量分布情况，从而找到订单量最多的时段和是否存在明显的下单高峰期。可以按小时或日期分组统计数据。 
SELECT
  EXTRACT(
    HOUR
    FROM
      create_time
  ) AS hour_of_day,
  COUNT(order_id) AS order_count
FROM
  orders
GROUP BY
  EXTRACT(
    HOUR
    FROM
      create_time
  )
ORDER BY
  hour_of_day;
-- 除了按小时分析外，还可以从每天的角度查看订单量的变化趋势，进一步确认是否存在某些天的订单量明显更高。 
SELECT
  DATE (create_time) AS order_date,
  COUNT(order_id) AS order_count
FROM
  orders
GROUP BY
  DATE (create_time)
ORDER BY
  order_date;
-- 为了更全面地了解用户下单行为，可以分析一周中每一天的订单量，判断是否有特定星期几订单量较高。 
SELECT
  EXTRACT(
    DOW
    FROM
      create_time
  ) AS day_of_week,
  COUNT(order_id) AS order_count
FROM
  orders
GROUP BY
  EXTRACT(
    DOW
    FROM
      create_time
  )
ORDER BY
  day_of_week;
-- 最后可以结合月份数据，观察全年订单量的分布情况，以识别是否存在季节性高峰。 
SELECT
  EXTRACT(
    MONTH
    FROM
      create_time
  ) AS month_of_year,
  COUNT(order_id) AS order_count
FROM
  orders
GROUP BY
  EXTRACT(
    MONTH
    FROM
      create_time
  )
ORDER BY
  month_of_year;

-- 通过分析男性和女性用户在商品类别上的购买数量和金额分布，可以揭示两者在商品选择上的偏好差异。 
SELECT
  p.category AS 商品类别,
  u.gender AS 性别,
  SUM(oi.quantity) AS 总购买数量,
  SUM(oi.subtotal) AS 总购买金额
FROM
  order_items oi
  JOIN products p ON oi.product_id = p.product_id
  JOIN orders o ON oi.order_id = o.order_id
  JOIN users u ON o.user_id = u.user_id
GROUP BY
  p.category,
  u.gender
ORDER BY
  总购买金额 DESC;
  -- 比较男性和女性用户的平均订单金额，观察是否存在显著差异。 
SELECT
  u.gender AS 性别,
  AVG(o.total_amount) AS 平均订单金额
FROM
  orders o
  JOIN users u ON o.user_id = u.user_id
GROUP BY
  u.gender;
-- 统计男性和女性用户购买的商品单价分布，分析其消费档次的差异。 
SELECT u.gender AS 性别, CASE WHEN p.price < 100 THEN '低价' WHEN p.price BETWEEN 100 AND 500 THEN '中价' ELSE '高价' END AS 价格区间, COUNT(*) AS 商品数量 FROM order_items oi JOIN products p ON oi.product_id = p.product_id JOIN orders o ON oi.order_id = o.order_id JOIN users u ON o.user_id = u.user_id GROUP BY u.gender, 价格区间 ORDER BY 商品数量 DESC;

-- 通过分析订单和商品数据，计算每类商品的复购率。复购率可以通过统计购买过某类商品的用户中再次购买该类商品的比例来定义。最终输出排名前五的商品类别及其复购率。
WITH user_category_purchase AS (
	SELECT
		o.user_id,
		P.category,
		COUNT(DISTINCT o.order_id) AS purchase_count 
	FROM
		orders o
		JOIN order_items oi ON o.order_id = oi.order_id
		JOIN products P ON oi.product_id = P.product_id 
	GROUP BY
		o.user_id,
P.category),
repurchase_rate AS (
	SELECT
		category,
SUM(CASE WHEN purchase_count > 1 THEN 1 ELSE 0 END) * 1.0 / COUNT(DISTINCT user_id) AS repurchase_rate 
FROM
	user_category_purchase 
GROUP BY
	category) SELECT
	category AS 商品类别,
	ROUND(repurchase_rate : : NUMERIC, 4) AS 复购率 
FROM
	repurchase_rate 
ORDER BY
	复购率 DESC 
	LIMIT 5;

-- 分析用户从注册到第一次购买的时间间隔，可以通过计算每个用户的注册时间和其第一次下单时间的差值。为了实现这一目标，需要将用户表（users）和订单表（orders）进行关联，并筛选出每个用户的最早订单时间。最终结果可以按时间间隔分组统计。 
WITH
  first_order_time AS (
    SELECT
      o.user_id,
      MIN(o.payment_time) AS first_payment_time
    FROM
      orders o
    GROUP BY
      o.user_id
  )
SELECT
  EXTRACT(
    DAY
    FROM
      (fo.first_payment_time - u.registration_time)
  ) AS days_to_first_purchase,
  COUNT(*) AS user_count
FROM
  users u
  JOIN first_order_time fo ON u.user_id = fo.user_id
WHERE
  fo.first_payment_time IS NOT NULL
GROUP BY
  days_to_first_purchase
ORDER BY
  days_to_first_purchase;

  -- 进一步分析用户从注册到第一次购买的时间间隔是否与用户的某些属性相关，例如性别或年龄。这可以帮助识别不同用户群体的行为模式差异。这里选择性别作为分组维度进行分析。 
WITH
  first_order_time AS (
    SELECT
      o.user_id,
      MIN(o.payment_time) AS first_payment_time
    FROM
      orders o
    GROUP BY
      o.user_id
  )
SELECT
  u.gender AS gender,
  EXTRACT(
    DAY
    FROM
      (fo.first_payment_time - u.registration_time)
  ) AS days_to_first_purchase,
  COUNT(*) AS user_count
FROM
  users u
  JOIN first_order_time fo ON u.user_id = fo.user_id
WHERE
  fo.first_payment_time IS NOT NULL
GROUP BY
  u.gender,
  days_to_first_purchase
ORDER BY
  u.gender,
  days_to_first_purchase;

-- 通过分析用户的出生日期，可以研究年龄段对从注册到首次购买时间的影响。将用户按年龄段分组，例如每10年为一个区间，观察不同年龄段用户的购买行为。
WITH first_order_time AS (
	SELECT
		o.user_id,
		MIN(o.payment_time) AS first_payment_time 
	FROM
		orders o 
	GROUP BY
o.user_id),
age_grouped_users AS (
	SELECT
		u.user_id,
	CASE
			
			WHEN EXTRACT (YEAR FROM AGE (u.birth_date)) < 20 THEN
			'Under 20' 
			WHEN EXTRACT (YEAR FROM AGE (u.birth_date)) BETWEEN 20 
			AND 29 THEN
				'20-29' 
				WHEN EXTRACT (YEAR FROM AGE (u.birth_date)) BETWEEN 30 
				AND 39 THEN
					'30-39' 
					WHEN EXTRACT (YEAR FROM AGE (u.birth_date)) BETWEEN 40 
					AND 49 THEN
						'40-49' ELSE '50+' 
						END AS age_group 
					FROM
				users u) SELECT
				agu.age_group AS age_group,
				EXTRACT (
					DAY 
					FROM
				(fo.first_payment_time - u.registration_time)) AS days_to_first_purchase,
				COUNT(*) AS user_count 
				FROM
					users u
					JOIN first_order_time fo ON u.user_id = fo.user_id
					JOIN age_grouped_users agu ON u.user_id = agu.user_id 
				WHERE
					fo.first_payment_time IS NOT NULL 
				GROUP BY
					agu.age_group,
					days_to_first_purchase 
				ORDER BY
			agu.age_group,
days_to_first_purchase;

-- 分析各商品类别的月度销售趋势，可以通过统计每个月每个类别的销售数量和销售金额来观察变化。这有助于了解哪些类别在某些月份表现较好，并为库存管理和促销策略提供支持。 
SELECT
  DATE_TRUNC ('month', o.create_time) AS month,
  p.category AS category,
  SUM(oi.quantity) AS total_quantity,
  SUM(oi.subtotal) AS total_sales
FROM
  orders o
  JOIN order_items oi ON o.order_id = oi.order_id
  JOIN products p ON oi.product_id = p.product_id
GROUP BY
  DATE_TRUNC ('month', o.create_time),
  p.category
ORDER BY
  month,
  category;