.. _grafana-demo:

示例程序
==================================================

.. contents:: 目录


1 - 品种盈亏图
--------------------------------------------------
.. figure:: /images/grafana/grafana-demo03.png

::

    -- 选择品种ID和计算每个品种的总盈亏（总盈亏=平仓盈亏+持仓盈亏-手续费）
    SELECT product_id, SUM(close_profit + position_profit - commission) as "总盈亏"
    -- 从持仓表 PositionSnap 中获取数据
    FROM PositionSnap
    -- 筛选条件为秒级别的时间戳等于给定时间范围内的最大时间戳，并且投资者ID在指定的列表中
    WHERE timestamp_seconds = (
      SELECT max(timestamp_seconds)
      FROM PositionSnap
      WHERE timestamp_seconds >= $__from / 1000 and timestamp_seconds < $__to / 1000
    )
    AND investor_id IN (${investor:csv})
    -- 按照品种ID进行分组
    GROUP BY product_id
    -- 只选取总盈亏不为0的品种
    HAVING "总盈亏" != 0
    -- 按照总盈亏降序排序
    ORDER BY "总盈亏" DESC

2 - 板块盈亏图
--------------------------------------------------
.. figure:: /images/grafana/grafana-demo08.png

::

    SELECT 
      COALESCE(json_extract(i.category_chinese, '$[0]'), '未分类') as category, 
      SUM(p.close_profit + p.position_profit - p.commission) as "总盈亏"
    -- 从持仓表 PositionSnap 中获取数据
    FROM 
      PositionSnap p
    -- 连接 InstrumentSnap 表来获取板块中文名
    JOIN 
      InstrumentSnap i ON p.product_id = i.product_id
    -- 筛选条件为秒级别的时间戳等于给定时间范围内的最大时间戳，并且投资者ID在指定的列表中
    WHERE 
      p.timestamp_seconds = (
        SELECT max(timestamp_seconds)
        FROM PositionSnap
        WHERE timestamp_seconds >= $__from / 1000 and timestamp_seconds < $__to / 1000
      )
      AND p.investor_id IN (${investor:csv})
    -- 按照 category_chinese 的第一个值进行分组，处理可能为空的情况
    GROUP BY 
      COALESCE(json_extract(i.category_chinese, '$[0]'), '未分类')
    -- 只选取总盈亏不为0的品种
    HAVING 
      SUM(p.close_profit + p.position_profit - p.commission) != 0
    -- 按照总盈亏降序排序
    ORDER BY 
      SUM(p.close_profit + p.position_profit - p.commission) DESC
    
    
3 - 当日盈亏时序图by品种
--------------------------------------------------
.. figure:: /images/grafana/grafana-demo04.png

::

    -- 选择秒级别时间戳、（平仓盈亏+持仓盈亏-手续费）的总和，以及品种ID
    SELECT timestamp_seconds, SUM(close_profit + position_profit - commission) as "", product_id
    -- 从持仓表 PositionSnap 中获取数据
    FROM PositionSnap
    -- 筛选条件为时间戳在给定的范围内（秒级别），并且投资者ID在指定的列表中
    WHERE timestamp_seconds >= $__from / 1000 and timestamp_seconds < $__to / 1000 and investor_id IN (${investor:csv})
    -- 按照时间戳和品种ID进行分组
    GROUP BY timestamp_seconds, product_id
    -- 按照时间戳升序排序
    ORDER BY timestamp_seconds ASC
    
3.1 - 只显示期货品种
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.. figure:: /images/grafana/grafana-demo09.png

::

    -- 选择秒级别时间戳、（平仓盈亏+持仓盈亏-手续费）的总和，品种ID，以及合约类型
    SELECT timestamp_seconds, SUM(close_profit + position_profit - commission) as "", product_id, ins_class
    -- 从持仓表 PositionSnap 中获取数据
    FROM PositionSnap
    -- 筛选条件为时间戳在给定的范围内（秒级别），投资者ID在指定的列表中，并且合约类型为期货
    WHERE timestamp_seconds >= $__from / 1000 and timestamp_seconds < $__to / 1000 and investor_id IN (${investor:csv}) and ins_class='FUTURE'
    -- 按照时间戳和品种ID进行分组
    GROUP BY timestamp_seconds, product_id
    -- 按照时间戳升序排序
    ORDER BY timestamp_seconds ASC
    
4 - 当日盈亏时序图by账户
--------------------------------------------------
.. figure:: /images/grafana/grafana-demo05.png

::

    -- 选择秒级别时间戳、计算出来的净利润（平仓盈亏 + 持仓盈亏 - 手续费），以及投资者ID
    SELECT timestamp_seconds,(close_profit + position_profit-commission) as "",investor_id
    -- 从账户资金表 AccountSnap 中获取数据
    FROM AccountSnap
    -- 筛选条件为时间戳在给定的范围内（秒级别），并且投资者ID在指定的列表中
    WHERE timestamp_seconds >= $__from / 1000 and timestamp_seconds < $__to / 1000 and investor_id IN (${investor:csv})
    -- 按照时间戳和投资者ID进行分组，这样每个投资者在每个时间点只会有一个净利润的值
    GROUP BY timestamp_seconds, investor_id
    -- 按照时间戳升序排序
    ORDER BY timestamp_seconds ASC    

5 - 账户权益表
--------------------------------------------------
.. figure:: /images/grafana/grafana-demo01.png

::

    -- 创建一个CTE (Common Table Expression)，只选择最新交易日和指定投资者的数据
    WITH FilteredAccountSnap AS (
      SELECT *
      FROM AccountSnap
      WHERE trading_day = (SELECT MAX(trading_day) FROM AccountSnap) AND investor_id IN (${investor:csv})
    ),
    -- 创建另一个CTE，对每个投资者的最大权益、最小权益和最大风险度进行聚合计算
    AggregatedAccountSnap AS (
      SELECT
        trading_day,
        investor_id,
        MAX(balance) AS max_balance,
        MIN(balance) AS min_balance,
        MAX(risk_ratio) AS max_risk_ratio
      FROM
        FilteredAccountSnap
      GROUP BY
        investor_id
    )
    -- 从AggregatedAccountSnap选择数据，并结合FilteredAccountSnap来找出最大权益、最小权益和最大风险度对应的时间
    SELECT
      AggregatedAccountSnap.trading_day as "交易日",
      AggregatedAccountSnap.investor_id as "账户",
      AggregatedAccountSnap.max_balance AS "每日最大权益",
      AggregatedAccountSnap.min_balance AS "每日最小权益",
      -- 使用CASE表达式找出最大权益对应的时间，并将Unix时间戳转换为日期时间格式
      datetime(MIN(CASE WHEN FilteredAccountSnap.balance = AggregatedAccountSnap.max_balance THEN FilteredAccountSnap.timestamp_seconds END), 'unixepoch', '+8 hours') AS "最大权益发生时间",
      -- 使用CASE表达式找出最小权益对应的时间，并将Unix时间戳转换为日期时间格式
      datetime(MIN(CASE WHEN FilteredAccountSnap.balance = AggregatedAccountSnap.min_balance THEN FilteredAccountSnap.timestamp_seconds END), 'unixepoch', '+8 hours') AS "最小权益发生时间",
      AggregatedAccountSnap.max_risk_ratio AS "每日最大风险度",
      -- 使用CASE表达式找出最大风险度对应的时间，并将Unix时间戳转换为日期时间格式
      datetime(MIN(CASE WHEN FilteredAccountSnap.risk_ratio = AggregatedAccountSnap.max_risk_ratio THEN FilteredAccountSnap.timestamp_seconds END), 'unixepoch', '+8 hours') AS "最大风险度发生时间"
    FROM
      AggregatedAccountSnap
    -- 通过投资者ID连接AggregatedAccountSnap和FilteredAccountSnap
    JOIN
      FilteredAccountSnap ON AggregatedAccountSnap.investor_id = FilteredAccountSnap.investor_id
    -- 按投资者ID进行分组
    GROUP BY
      AggregatedAccountSnap.investor_id
    
    
6 - 品种保证金占用（饼图）
--------------------------------------------------
.. figure:: /images/grafana/grafana-demo02.png

::

    -- 选择保证金和品种ID
    SELECT SUM(margin) as "VALUES", product_id 
    -- 从持仓表 PositionSnap 中获取数据
    FROM PositionSnap  
    -- 筛选最近一个截面的、且保证金不为0数据
    where timestamp_seconds = (SELECT max(timestamp_seconds) from PositionSnap) and margin !=0 and investor_id IN (${investor:csv})
    -- 按照品种ID进行分组   
    GROUP BY product_id


7 - 市值分布（树状图）
--------------------------------------------------
.. figure:: /images/grafana/grafana-demo06.png

::

    -- 选择品种ID和市值权益的总和
    SELECT SUM(market_value) as "VALUES", product_id
    -- 从持仓表 PositionSnap 中获取数据
    FROM PositionSnap  
    -- 筛选买卖方向为买入，给定时间范围内的最大时间戳，市值权益不为0，并且投资者ID在指定的列表中
    where direction = "BUY" and timestamp_seconds = (SELECT max(timestamp_seconds) from PositionSnap) and market_value !=0 and investor_id IN (${investor:csv})   
    -- 按照品种ID进行分组
    GROUP BY product_id