使用包含每日日期的列按月分组

Question

我需要在下面的代码中添加一个额外的'group by'字段，以便根据trade_date字段按月进行分组。代码当前生成的表的列头与开始的'select'语句中的4个字段匹配。我需要添加一个5，它使用trade_date字段按月分组，而不是按天分组。任何想法吗?

select  
TRADEABLE_INSTR_NAME, 
TRADING_USERID, 
UNDERLYING_SYM_BLOOMBERG,
SUM(DMA_FEE) AS DMA_FEE_SUBTOTAL
FROM
(
SELECT

a.transaction_id,
b.amount,
b.charge_id,
c.charge_type_id,
d.charge_group,
e.account_name,
f.sig_entity_label,
e.trading_userid,
e.exchange_id,
e.tradeable_instr_name,
g.instr_subtype_label,
g.underlying_sym_bloomberg,
g.fut_expiration_date,
e.trade_date,
timestamp_table.timeformated,
a.rate * a.basis_value AS dma_fee,
SUM(A.RATE * A.BASIS_VALUE) OVER () as DMA_FEE_TOTAL
FROM
repldoadm.ire_estimate_trans_map       a
LEFT JOIN repldoadm.ire_charges_estimate         b ON a.estimate_id = b.estimate_id
LEFT JOIN repldoadm.ire_charges_lu               c ON b.charge_id = c.charge_id
LEFT JOIN repldoadm.ire_charge_types_lu          d ON c.charge_type_id = d.charge_type_id
LEFT JOIN repldoadm.vw_ire_trade_transactions    e ON a.transaction_id = e.trans_id
LEFT JOIN (
SELECT
to_char(create_ts, 'HH24:MI:SS') AS timeformated,
trans_id
FROM
repldoadm.vw_ire_trade_transactions
)                                      timestamp_table ON ( a.transaction_id = timestamp_table.trans_id )
LEFT JOIN repldoadm.vw_ire_accounts              f ON e.account_name = f.pb_account_name
LEFT JOIN stig_adm.instrument_universe           g ON e.tradeable_instr_name = g.short_name
WHERE
timeformated >= '07:00:00'
AND timeformated <= '18:00:00'
AND fut_expiration_date >= add_months(trunc(sysdate), - 12)
AND trade_date = '02-AUG-21'
AND e.exchange_id = '30'
AND charge_group = 'EXECUTION_BROKER_CHARGES'
AND estimate_run = 'Late'
--AND transaction_id IN ( '791088074', '791309067' )
)
group by TRADEABLE_INSTR_NAME, TRADING_USERID, UNDERLYING_SYM_BLOOMBERG;

Answer 1

Group byTRUNC(trade_date, 'MM')

SELECT TRADEABLE_INSTR_NAME, 
TRADING_USERID, 
UNDERLYING_SYM_BLOOMBERG,
SUM(DMA_FEE) AS DMA_FEE_SUBTOTAL,
TRUNC( trade_date, 'MM' ) AS trade_month
FROM   (
<your huge sub-query>
)
group by
TRADEABLE_INSTR_NAME,
TRADING_USERID,
UNDERLYING_SYM_BLOOMBERG,
TRUNC(trade_date, 'MM');

---

您的查询将有效地为:

select TRADEABLE_INSTR_NAME, 
TRADING_USERID, 
UNDERLYING_SYM_BLOOMBERG,
TRUNC(trade_date, 'MM') AS trade_month
SUM(DMA_FEE) AS DMA_FEE_SUBTOTAL
FROM   (
SELECT e.trading_userid,
e.tradeable_instr_name,
g.underlying_sym_bloomberg,
e.trade_date,
a.rate * a.basis_value AS dma_fee
FROM   repldoadm.ire_estimate_trans_map a
INNER JOIN repldoadm.ire_charges_estimate b
ON a.estimate_id = b.estimate_id
INNER JOIN repldoadm.ire_charges_lu c
ON b.charge_id = c.charge_id
INNER JOIN repldoadm.ire_charge_types_lu d
ON c.charge_type_id = d.charge_type_id
INNER JOIN repldoadm.vw_ire_trade_transactions e
ON (   a.transaction_id = e.trans_id
AND TO_CHAR(e.create_ts, 'HH24:MI:SS') BETWEEN '07:00:00'
AND '18:00:00'
)
LEFT JOIN repldoadm.vw_ire_accounts f
ON e.account_name = f.pb_account_name
INNER JOIN stig_adm.instrument_universe g
ON e.tradeable_instr_name = g.short_name
WHERE  g.fut_expiration_date >= add_months(trunc(sysdate), - 12)
AND    e.trade_date = DATE '2021-08-02'
AND    e.exchange_id = '30'
AND    d.charge_group = 'EXECUTION_BROKER_CHARGES'
AND    estimate_run = 'Late'
--AND    transaction_id IN ( '791088074', '791309067' )
)
group by
TRADEABLE_INSTR_NAME,
TRADING_USERID,
UNDERLYING_SYM_BLOOMBERG,
TRUNC(trade_date, 'MM');

注意:由于您在WHERE子句中要求非NULL值，因此您的LEFT JOINs变为INNER JOINs。

注意:您不需要在内部查询中SELECT不在外部查询中使用的列。

注意:我不知道你是否需要加入repldoadm.vw_ire_accounts f，因为你似乎没有使用它。