/**查詢所有網(wǎng)元的所有粒度**/
SELECT
EMS_PM_LOG_ID,
SUBNET_ID,
AMOID,
NE_TYPE,
PO_ID,
PO_TABLE,
GP_BEGIN_TIME,
LOG_TYPE,
MIN(LOG_TIME) MIN_END_TIME,
LOG_TIME,
MAX(LOG_TIME) MAX_END_TIME,
TIMESTAMPDIFF(MINUTE,GP_BEGIN_TIME,MAX(LOG_TIME))-15 DELAY_TIME,
COUNT(*) num
FROM
EMS_PM_LOG
GROUP BY
AMOID,
GP_BEGIN_TIME,
LOG_TYPE
ORDER BY
GP_BEGIN_TIME,AMOID DESC;
/**查詢一個(gè)網(wǎng)元的所有粒度**/
SELECT
AMOID,
GP_BEGIN_TIME,
LOG_TYPE,
MIN(LOG_TIME) min_end_time,
MAX(LOG_TIME) max_end_time,
COUNT(*) num
FROM
EMS_PM_LOG
WHERE
AMOID = 'iw5y99gk-3'
AND LOG_TYPE = 'download'
GROUP BY
AMOID,
GP_BEGIN_TIME;
/**查詢一個(gè)粒度的所有網(wǎng)元**/
SELECT
AMOID,
GP_BEGIN_TIME,
LOG_TYPE,
MIN(LOG_TIME) min_end_time,
MAX(LOG_TIME) max_end_time
FROM
EMS_PM_LOG
WHERE
LOG_TYPE = 'transform'
AND GP_BEGIN_TIME = '2017-02-13 09:15:00'
GROUP BY
AMOID;
/*查詢一個(gè)網(wǎng)元，一個(gè)粒度*****/
SELECT
AMOID,
GP_BEGIN_TIME,
LOG_TIME
FROM
EMS_PM_LOG
WHERE
AMOID = 'iw5y99gk-3'
AND LOG_TYPE = 'download'
AND GP_BEGIN_TIME = '2017-02-13 09:15:00'
ORDER BY
LOG_TIME DESC;
SELECT
AMOID,
GP_BEGIN_TIME,
LOG_TIME
FROM
EMS_PM_LOG
WHERE
AMOID = 'iwbodzuq-2'
AND LOG_TYPE = 'transform'
AND GP_BEGIN_TIME = '2017-02-13 09:15:00'
ORDER BY
LOG_TIME DESC;
SELECT
AMOID,
GP_BEGIN_TIME,
LOG_TIME
FROM
EMS_PM_LOG
WHERE
AMOID = 'iwbodzuq-2'
AND LOG_TYPE = 'transform'
AND GP_BEGIN_TIME = '2017-02-13 09:15:00'
ORDER BY
LOG_TIME DESC;
###-- 某個(gè)LOG_TYPE【download、transform、create zdc】相對于粒度結(jié)束時(shí)間的入庫延遲，單位是秒
SELECT
EMS_PM_LOG_ID,
SUBNET_ID,
NE_TYPE,
PO_ID,
PO_TABLE,
LOG_TYPE,
AMOID,
GP_BEGIN_TIME,
LOG_TIME,
MAX(LOG_TIME) MAX_END_TIME,
TIMESTAMPDIFF(
MINUTE,
GP_BEGIN_TIME,
MAX(LOG_TIME)
) - 15 PM_STORAGE_DELAY_TIME
FROM
EMS_PM_LOG
WHERE
1 = 1
GROUP BY
AMOID,
GP_BEGIN_TIME,
LOG_TYPE
ORDER BY
PM_STORAGE_DELAY_TIME DESC;
