Oracle分析函数

自己理解的使用背景：

只需要表中或者C#结果集中的一条数据时，往这想！！！！！！

在多表关联的SQL查询中，只需要其中一个表的特定的一条数据，可使用分析函数！！！

普通的聚合函数用group by分组，每个分组返回一个统计值；而分析函数采用partition by 分组，并且每组每行都可以返回一个统计值。

比如用group by分组后，左表是被分组了，但是导致右表的数据可能是随机的，不是想要的那个右表数据，这时候就可以用分析函数采用partition by分组

C#代码中举例：

rownum = Sql.Ext.RowNumber()
        .Over()
        .PartitionBy(p.MITEM_CODE)
        .OrderBy(p.MITEM_CODE)
        .ThenBy(p.MITEM_CODE)
        .ToValue(),

SQL举例：

WITH A AS
 (SELECT P1.ORG_ID,
         P1.EQUIPMENT_CODE,
         P1.EQUIPMENT_NAME,
         P1.AREA_CODE,
         P1.SHIFT_CODE,
         P1.EQUPMENT_OPERATION_STATUS,
         P1.PRE_OPERATION_STATUS,
         P1.STATION_CODE,
         P1.START_STANDBY_TIME,
         P1.START_RUN_TIME,
         P2.WORKSHOP_CODE,
         P2.OCCURRED_TIME AS ABNORMAL_OCCURRENCE_TIME,
         P2.INCIDENT_DETAIL AS ABNORMAL_DETAIL,
         ROW_NUMBER() OVER(PARTITION BY P1.ORG_ID, P1.EQUIPMENT_CODE, P1.EQUIPMENT_NAME, P1.AREA_CODE, P1.SHIFT_CODE, P1.EQUPMENT_OPERATION_STATUS, P1.PRE_OPERATION_STATUS, P1.STATION_CODE, P1.START_STANDBY_TIME, P1.START_RUN_TIME, P2.WORKSHOP_CODE ORDER BY P1.START_STANDBY_TIME - P2.OCCURRED_TIME ASC) AS RN
    FROM FND_EQUPMINET_OPERATION_STATUS P1
   INNER JOIN AD_INCIDENT_LOG P2
      ON (P1.EQUIPMENT_CODE = P2.EQUIPMENT_CODE)
   WHERE P1.PRE_OPERATION_STATUS IN ('1', '4')
     AND P2.EQUIPMENT_TYPE_CODE LIKE '%AUTO%'
     AND P1.START_STANDBY_TIME >= P2.OCCURRED_TIME
     AND P2.OCCURRED_TIME >= P1.START_STANDBY_TIME - INTERVAL '24' HOUR
     AND P1.ORG_ID = 'C03'
     AND P2.WORKSHOP_CODE = '1'
     AND P1.STATION_CODE IN ('KS1')
     AND P1.START_STANDBY_TIME >=
         TO_DATE('2024-03-11 08:00:00', 'yyyy-MM-dd hh24:mi:ss')
     AND P1.START_STANDBY_TIME <=
         TO_DATE('2024-03-11 10:00:00', 'yyyy-MM-dd hh24:mi:ss')
 )
SELECT *
  FROM A
 WHERE RN = 1
 ORDER BY EQUIPMENT_CODE,
          AREA_CODE,
          START_STANDBY_TIME,
          ABNORMAL_OCCURRENCE_TIME