过滤事务每日wise

Question

1. 我有两个带有主键(Agent_ID)的表。我想加入两个表，过滤Agent_Type =3、status =0和Transaction=5
2. 获取最后一个活动年的总交易价值，每日明智，谁是没有做任何事务，在过去三个月。

我在下面的代码中试过了，但是我没有得到正确的答案

SELECT
AGENT_ID,
TO_CHAR(TO_DATE(TX_TIME,'DD-MON-YYYY')) AS DAYMONTHYEAR,
SUM(TX_VALUE) AS TOTALDAILYWISE,
count(*)as Transaction_count
FROM
(
    SELECT
        A.AGENT_ID,
        TO_DATE(ATR.TX_TIME,'DD-MON-YYYY') AS TX_TIME,
        MAX(TO_DATE(ATR.TX_TIME,'DD-MON-YYYY')) OVER(
            PARTITION BY A.AGENT_ID
        ) AS LAST_TR_DATE,
        ATR.TX_VALUE,
        A.AGENT_TYPE_ID
    FROM
        TBLEZ_AGENT A
        JOIN TBLEZ_TRANSACTION ATR ON ( A.AGENT_ID = ATR.SRC_AGENT_ID )
    WHERE
        A.AGENT_TYPE_ID = '3'
        AND ATR.STATUS = '0'
        AND ATR.TX_TYPE_ID = '5'
)
WHERE
LAST_TR_DATE < (SYSDATE - 90)
AND ( TX_TIME BETWEEN(LAST_TR_DATE - 365) AND LAST_TR_DATE )
GROUP BY
AGENT_ID,
TX_TIME;

请支持如何获得日常事务的智慧。

Answer 1

请尝试以下查询：

SELECT
    AGENT_ID,
    TO_CHAR(TO_DATE(TX_TIME, 'DD-MON-YYYY')) AS DAYMONTHYEAR,
    SUM(TX_VALUE) AS TOTALMONTHWISE,
    COUNT(*) AS TRANSACTION_COUNT
FROM
    (
        SELECT
            A.AGENT_ID,
            TO_DATE(ATR.TX_TIME, 'DD-MON-YYYY') AS TX_TIME,
            MAX(TO_DATE(ATR.TX_TIME, 'DD-MON-YYYY')) OVER(
                PARTITION BY A.AGENT_ID
            ) AS LAST_TR_DATE,
            ATR.TX_VALUE,
            A.AGENT_TYPE_ID
        FROM
            TBLEZ_AGENT A
            JOIN TBLEZ_TRANSACTION ATR ON ( A.AGENT_ID = ATR.SRC_AGENT_ID )
        WHERE
            A.AGENT_TYPE_ID = '3'
            AND ATR.STATUS = '0'
            AND ATR.TX_TYPE_ID = '5'
    )
WHERE
    LAST_TR_DATE < TRUNC(ADD_MONTHS( SYSDATE, - 3 ))
    AND  TX_TIME BETWEEN ADD_MONTHS(LAST_TR_DATE, -13) AND LAST_TR_DATE - 1 
GROUP BY
    AGENT_ID,
    TX_TIME;

干杯！！