Evidence：SQL + Markdown 生成数据报告网站¶

编写 Markdown + SQL
       │
       ▼
Evidence 开发服务器 (实时预览)
       │
       ▼
执行 SQL → 获取数据 → 渲染图表 → 生成页面
       │
       ▼
evidence build → 静态网站 → 部署

# 使用官方脚手架
npx degit evidence-dev/template my-report
cd my-report

# 安装依赖
npm install

# 启动开发服务器
npm run dev

# 在开发模式下访问 http://localhost:3000/settings
# 可以通过 UI 配置数据源

# 或手动编辑 evidence.plugins.yaml

# evidence.plugins.yaml
datasources:
  - postgres:
      host: localhost
      port: 5432
      database: analytics
      user: readonly
      password: ${POSTGRES_PASSWORD}  # 使用环境变量

# 将 CSV 文件放在 sources/ 目录下
mkdir -p sources/local
cp sales_data.csv sources/local/
# Evidence 会自动将它注册为可查询的表

---
title: 销售仪表板
---

# 销售概览

```sql sales_summary
SELECT 
    date_trunc('month', order_date) as month,
    COUNT(*) as order_count,
    SUM(amount) as revenue
FROM orders
GROUP BY 1
ORDER BY 1
```

<LineChart 
    data={sales_summary} 
    x=month 
    y=revenue 
    title="月度收入趋势"
/>

## 关键指标

<BigValue 
    data={sales_summary} 
    value=revenue 
    title="总收入"
    fmt="$#,##0"
/>

<BigValue 
    data={sales_summary} 
    value=order_count 
    title="总订单数"
/>

## 明细数据

<DataTable data={sales_summary} rows=10 />

```sql csv_data
SELECT * FROM local.sales_data
WHERE region = 'East'
ORDER BY revenue DESC
LIMIT 20
```

<BarChart data={csv_data} x=product y=revenue />

pages/
├── index.md            → /
├── sales.md            → /sales
├── customers/
│   ├── index.md        → /customers
│   └── [id].md         → /customers/123 (参数化)
└── reports/
    └── monthly.md      → /reports/monthly

## 折线图
<LineChart data={query_result} x=date y=value />

## 面积图
<AreaChart data={query_result} x=date y={["sales", "costs"]} />

## 柱状图
<BarChart data={query_result} x=category y=count sort=true />

## 散点图
<ScatterPlot data={query_result} x=price y=rating size=volume />

## 饼图
<PieChart data={query_result} name=category value=percentage />

## 漏斗图
<FunnelChart data={funnel_data} name=stage value=users />

## 热力图
<Heatmap data={query_result} x=day y=hour value=count />

## 地图
<USMap data={state_data} state=state_name value=population />

---
title: 客户详情
---

# 客户 {params.customer_id} 的详情

```sql customer_info
SELECT * FROM customers
WHERE customer_id = '${params.customer_id}'
```

```sql customer_orders
SELECT * FROM orders
WHERE customer_id = '${params.customer_id}'
ORDER BY order_date DESC
```

<DataTable data={customer_info} />

## 订单历史

<LineChart data={customer_orders} x=order_date y=amount />

```sql all_regions
SELECT DISTINCT region FROM sales ORDER BY region
```

<Dropdown 
    name=selected_region 
    data={all_regions} 
    value=region 
    title="选择区域"
/>

```sql filtered_sales
SELECT * FROM sales
WHERE region = '${inputs.selected_region}'
```

<BarChart data={filtered_sales} x=product y=revenue />

```sql revenue_check
SELECT SUM(revenue) as total FROM sales WHERE month = current_month
```

{#if revenue_check[0].total > 1000000}
  <Alert status="success">本月收入已超百万！</Alert>
{:else}
  <Alert status="warning">本月收入未达标</Alert>
{/if}

```sql departments
SELECT DISTINCT department FROM employees ORDER BY department
```

{#each departments as dept}
  ## {dept.department} 部门

  ```sql dept_stats
  SELECT COUNT(*) as headcount, AVG(salary) as avg_salary
  FROM employees WHERE department = '${dept.department}'
  ```

  - 人数: {dept_stats[0].headcount}
  - 平均薪资: {fmt(dept_stats[0].avg_salary, '$#,##0')}
{/each}

<Grid cols=3>
    <BigValue data={kpi1} value=metric title="DAU" />
    <BigValue data={kpi2} value=metric title="收入" fmt="$#,##0" />
    <BigValue data={kpi3} value=metric title="转化率" fmt="0.0%" />
</Grid>

<Tabs>
    <Tab label="日视图">
        <LineChart data={daily} x=date y=value />
    </Tab>
    <Tab label="周视图">
        <LineChart data={weekly} x=week y=value />
    </Tab>
</Tabs>

# 构建静态网站
npm run build

# 输出在 build/ 目录，可以部署到：
# - Vercel
# - Netlify
# - GitHub Pages
# - 任何静态文件服务器

# 预览构建结果
npm run preview

npm i -g vercel
vercel --prod

# 使用 GitHub Actions 定时重建
# .github/workflows/refresh.yml
name: Refresh Report
on:
  schedule:
    - cron: '0 8 * * *'  # 每天早8点
  workflow_dispatch:

jobs:
  build:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with:
          node-version: 20
      - run: npm ci
      - run: npm run build
        env:
          POSTGRES_PASSWORD: ${{ secrets.POSTGRES_PASSWORD }}
      - uses: actions/upload-pages-artifact@v3
        with:
          path: build

# 开发模式下，错误会直接显示在页面上
npm run dev

# 查看详细日志
npm run dev -- --verbose