时间戳

首页 > 解决方案 > 每日过滤交易

sql - 每日过滤交易

问题描述

  1. 我有两个带有主键（Agent_ID）的表。我想加入两个表，过滤器Agent_Type =3和status =0Transaction=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;

请支持如何每天明智地进行交易

标签： sql

解决方案

试试这个查询：

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;

干杯！！

推荐阅读