如何为Grafana SQLite交易统计查询添加首行汇总行

正确实现方案 #

你可以通过UNION ALL拼接汇总行和原有按天统计结果实现需求，推荐使用CTE封装基础统计逻辑，避免重复计算，SQL代码如下：

WITH daily_stats AS (
    SELECT
        date(close_date) AS "Date",
        COUNT(CASE WHEN close_profit > 0 THEN 1 END) AS Wins,
        COUNT(CASE WHEN close_profit < 0 THEN 1 END) AS Losses,
        AVG(close_profit * 100) AS "Avg",
        SUM(close_profit_abs) AS "Profit"
    FROM trades 
    WHERE date(close_date) IS NOT NULL 
    GROUP BY date(close_date)
)
-- 汇总行查询
SELECT 
    'Total' AS "Date",
    SUM(Wins) AS Wins,
    SUM(Losses) AS Losses,
    AVG("Avg") AS "Avg",
    SUM(Profit) AS "Profit",
    1 AS sort_order
FROM daily_stats

UNION ALL

-- 原按天统计查询
SELECT 
    "Date",
    Wins,
    Losses,
    "Avg",
    Profit,
    2 AS sort_order
FROM daily_stats

ORDER BY sort_order, "Date" DESC

逻辑说明 #

1. 先通过CTEdaily_stats封装按天统计的基础逻辑，替换了你原有的多层嵌套子查询，执行效率更高，也更易维护
2. 第一部分查询基于daily_stats聚合得到汇总行，完全匹配你要求的指标计算规则：总胜场、总败场、每日平均收益率的均值、总利润
3. 通过自定义的sort_order字段控制排序，保证汇总行永远排在表格第一行，后续数据按日期倒序排列，和你预期的输出格式完全一致

内容的提问来源于stack exchange，提问作者user3647376