hive写法：表中有用户id，每个用户有多个单号，对应不同时间，现要提取每个客户近1个月单数、近3个月单数、近6个月单数、近9个月单数

在 Apache Hive 中，要完成这样的查询，你需要使用日期函数来处理时间范围，并使用 GROUP BY 和子查询来获取每个客户的近几个月的订单数量。假设有一个名为 `orders` 的表，其中包含 `user_id`, `order_id`, 和 `order_date` 字段，你可以尝试下面的Hive SQL 写法：

CREATE TABLE temp_orders AS
WITH monthly_counts AS (
  SELECT 
    user_id,
    DATE_TRUNC('month', order_date) as month_key,
    COUNT(order_id) as orders_this_month
  FROM orders
  WHERE order_date >= current_date - INTERVAL '1 MONTH'
  GROUP BY user_id, month_key
),
three_month_counts AS (
  SELECT 
    user_id,
    DATE_TRUNC('month', order_date) as month_key,
    COUNT(order_id) as orders_last_3_months
  FROM orders
  WHERE order_date >= current_date - INTERVAL '3 MONTH' AND order_date < current_date - INTERVAL '1 MONTH'
  GROUP BY user_id, month_key
),
six_month_counts AS (
  SELECT 
    user_id,
    DATE_TRUNC('month', order_date) as month_key,
    COUNT(order_id) as orders_last_6_months
  FROM orders
  WHERE order_date >= current_date - INTERVAL '6 MONTH' AND order_date < current_date - INTERVAL '3 MONTH'
  GROUP BY user_id, month_key
),
nine_month_counts AS (
  SELECT 
    user_id,
    DATE_TRUNC('month', order_date) as month_key,
    COUNT(order_id) as orders_last_9_months
  FROM orders
  WHERE order_date >= current_date - INTERVAL '9 MONTH' AND order_date < current_date - INTERVAL '6 MONTH'
  GROUP BY user_id, month_key
)
SELECT 
  t1.user_id,
  t1.orders_this_month as recent_1_month,
  t2.orders_last_3_months as recent_3_months,
  t3.orders_last_6_months as recent_6_months,
  t4.orders_last_9_months as recent_9_months
FROM 
  temp_orders t1
LEFT JOIN three_month_counts t2 ON t1.user_id = t2.user_id AND t1.month_key = t2.month_key
LEFT JOIN six_month_counts t3 ON t1.user_id = t3.user_id AND t1.month_key = t3.month_key
LEFT JOIN nine_month_counts t4 ON t1.user_id = t4.user_id AND t1.month_key = t4.month_key
ORDER BY user_id;

请注意，这只是一个示例，实际运行前需要检查日期函数、当前日期函数（如 `current_date`）是否适用于你的Hive版本。同时，`DATE_TRUNC` 函数可能会因数据库的不同而有所差异。