# 1. 安装 Node.js（v18+）
# 2. 创建 Evidence 项目
npm create evidence@latest my-dashboard
cd my-dashboard

# 3. 安装 DuckDB 插件
npm install @evidence-dev/duckdb

# 4. 启动开发服务器
npm run dev

-- 在 DuckDB CLI 中执行，生成示例数据
COPY (
  SELECT 
    range::INTEGER + 1 AS order_id,
    strftime(date '2025-01-01' + INTERVAL (range % 365) DAY, '%Y-%m-%d') AS order_date,
    CASE WHEN range % 5 = 0 THEN '电子产品'
         WHEN range % 5 = 1 THEN '服装'
         WHEN range % 5 = 2 THEN '食品'
         WHEN range % 5 = 3 THEN '家居用品'
         ELSE '书籍文具' END AS category,
    CASE WHEN range % 20 = 0 THEN '北京旗舰店'
         WHEN range % 20 = 1 THEN '上海店'
         WHEN range % 20 = 2 THEN '广州店'
         WHEN range % 20 = 3 THEN '深圳店'
         WHEN range % 20 = 4 THEN '杭州店'
         WHEN range % 20 = 5 THEN '成都店'
         WHEN range % 20 = 6 THEN '武汉店'
         WHEN range % 20 = 7 THEN '南京店'
         WHEN range % 20 = 8 THEN '重庆店'
         WHEN range % 20 = 9 THEN '西安店'
         ELSE '线上渠道' END AS store,
    ROUND(50 + (range % 100) * 1.5 + (range % 30)::DOUBLE, 2) AS unit_price,
    (range % 20) + 1 AS quantity,
    ROUND((50 + (range % 100) * 1.5 + (range % 30)) * ((range % 20) + 1), 2) AS amount,
    CASE WHEN range % 3 = 0 THEN '新客'
         WHEN range % 3 = 1 THEN '老客'
         ELSE 'VIP' END AS customer_type
  FROM generate_series(0, 99999)
) TO 'data/sales_sample.parquet' (FORMAT PARQUET);

# sources/duckdb/connection.yaml
name: duckdb
type: duckdb
filename: my_dashboard.duckdb  # DuckDB 数据库文件
options:
  memory_limit: 2GB
  threads: 4

-- sources/duckdb/init.sql
-- 将 Parquet 数据加载到 DuckDB 中
CREATE OR REPLACE VIEW sales AS
SELECT * FROM read_parquet('data/sales_sample.parquet');

-- 创建月度汇总视图
CREATE OR REPLACE VIEW monthly_sales AS
SELECT 
  strftime(order_date, '%Y-%m') AS month,
  category,
  store,
  SUM(amount) AS revenue,
  COUNT(*) AS order_count,
  SUM(quantity) AS total_units,
  ROUND(AVG(amount), 2) AS avg_order_value
FROM sales
GROUP BY month, category, store;

---
title: 月度销售报告
---

# 📊 月度销售报告

**数据周期：** 2025年1月 - 2025年12月

---

## 📈 月度收入趋势

```sql monthly_revenue
SELECT 
  month,
  SUM(revenue) AS total_revenue,
  SUM(order_count) AS total_orders
FROM monthly_sales
GROUP BY month
ORDER BY month

SELECT 
  SUM(revenue) AS revenue,
  SUM(order_count) AS orders,
  COUNT(DISTINCT store) AS active_stores,
  ROUND(SUM(revenue) / SUM(order_count), 2) AS avg_order
FROM monthly_sales
WHERE month = (SELECT MAX(month) FROM monthly_sales)

SELECT 
  store,
  SUM(revenue) AS total_revenue,
  SUM(order_count) AS total_orders,
  ROUND(AVG(avg_order_value), 2) AS avg_order_value,
  ROUND(SUM(revenue) * 100.0 / SUM(SUM(revenue)) OVER(), 1) AS revenue_pct
FROM monthly_sales
GROUP BY store
ORDER BY total_revenue DESC

SELECT 
  month,
  category,
  SUM(revenue) AS revenue
FROM monthly_sales
GROUP BY month, category
ORDER BY month, category

SELECT 
  strftime(order_date, '%Y-%m') AS month,
  customer_type,
  SUM(amount) AS revenue,
  COUNT(*) AS orders
FROM sales
GROUP BY month, customer_type
ORDER BY month, customer_type

---
title: 销售分析
---

# 💰 销售深度分析

## 筛选条件

```sql stores_list
SELECT DISTINCT store FROM sales ORDER BY store

SELECT 
  MIN(order_date) AS min_date,
  MAX(order_date) AS max_date
FROM sales

WITH product_revenue AS (
  SELECT 
    category,
    SUM(amount) AS revenue
  FROM sales
  WHERE 1=1
    AND store = '${inputs.selected_store.value}' 
    OR '${inputs.selected_store.value}' = '__all__'
  GROUP BY category
),
cumulative AS (
  SELECT 
    category,
    revenue,
    SUM(revenue) OVER (ORDER BY revenue DESC) AS running_total,
    SUM(revenue) OVER () AS total_revenue
  FROM product_revenue
)
SELECT 
  category,
  revenue,
  ROUND(revenue * 100.0 / total_revenue, 1) AS pct,
  ROUND(running_total * 100.0 / total_revenue, 1) AS cumulative_pct
FROM cumulative
ORDER BY revenue DESC

# 只需更新数据文件，推送 git 即可触发自动部署
0 2 * * * cd /path/to/my-dashboard && \
  duckdb my_dashboard.duckdb < sources/duckdb/refresh.sql && \
  git add -A && \
  git commit -m "auto update $(date +%Y-%m-%d)" && \
  git push origin main