PostgreSQL函数问题：忽略同一卡号连续出现的首个入场记录

函数功能 #

• 从properties表获取时长限制参数（天数和日期）
• 构建动态SQL查询fact_entriesexits表，过滤日期和设备类型
• 匹配入场（devicetype=1）和离场（devicetype=2）事件，插入到fact_parkingtransaction表
• 针对不同场景输出提示信息
• 更新properties表的DURATION.LIMIT.DATE参数

核心问题 #

同一cardnumber的连续入场记录中，首个入场记录应被忽略，但现有函数未实现该逻辑。例如卡号00D56029在2023-05-02有两条连续入场记录，预期忽略第一条（2023-05-02 15:32:26.000），但实际该记录仍被匹配离场记录。

样本数据 #

devicetype|cardnumber|eventdate              |
----------+----------+-----------------------+
         1|00D56029  |2023-05-02 13:47:06.000|
         2|00D56029  |2023-05-02 15:31:00.000|
         1|00D56029  |2023-05-02 15:32:26.000|
         1|00D56029  |2023-05-02 15:33:33.000|
         2|00D56029  |2023-05-02 15:37:25.000|
         1|00D56029  |2023-07-25 14:20:56.000|
         2|00D56029  |2023-07-25 16:26:53.000|

预期输出 #

• 行1：入场2023-05-02 13:47:06.000 | 离场2023-05-02 15:31:00.000
• 行2：入场2023-05-02 15:33:33.000 | 离场2023-05-02 15:37:25.000
• 行3：入场2023-07-25 14:20:56.000 | 离场2023-07-25 16:26:53.000

实际输出 #

• 行1：入场2023-05-02 13:47:06.000 | 离场2023-05-02 15:31:00.000
• 行2：入场2023-05-02 15:32:28.000 | 离场2023-05-02 15:37:25.000
• 行3：入场2023-07-25 14:20:56.000 | 离场2023-07-25 16:26:53.000

当前函数代码 #

CREATE OR REPLACE FUNCTION public.desi3()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
    arrived_entry RECORD;
    departed_entry RECORD;  
    durationLimitDays INTEGER := -1;
    durationLimitDate TIMESTAMP := '1970-01-01 00:00:00';
    cursorQuery text;
    cursorEvent refcursor;
    maxEventTime TIMESTAMP;
BEGIN
  -- Get the duration limit parameters
  SELECT PROP_VALUE INTO durationLimitDays FROM properties WHERE prop_key = 'DURATION.LIMIT.DAYS';
  SELECT PROP_VALUE INTO durationLimitDate FROM properties WHERE prop_key = 'DURATION.LIMIT.DATE';

  cursorQuery := '
    WITH filtered_entries AS (
      SELECT
        id,
        facilitykey,
        systeminterfacekey,
        devicekey,
        datekey,
        timekey,
        tickettypekey,
        eventtypekey,
        manufacturerkey,
        devicetype,
        licenseplate,
        licenseplatekey,
        cardnumber,
        eventdate,
        etlsource,
        LAG(devicetype) OVER (PARTITION BY cardnumber ORDER BY eventdate) AS prev_devicetype,
        LEAD(devicetype) OVER (PARTITION BY cardnumber ORDER BY eventdate) AS next_devicetype
      FROM
        fact_entriesexits
      WHERE
        eventdate >= ' || quote_literal(durationLimitDate) || '
        AND devicetype IN (1, 2)
    ),
    valid_entries AS (
      SELECT
        fe.*,
        CASE
          WHEN devicetype = 1 AND (prev_devicetype IS NULL OR prev_devicetype = 2 OR (next_devicetype = 1 AND LEAD(next_devicetype) OVER (PARTITION BY cardnumber ORDER BY eventdate) = 2)) THEN 1
          WHEN devicetype = 2 AND (next_devicetype IS NULL OR next_devicetype = 1 OR prev_devicetype = 1) THEN 2
          ELSE NULL
        END AS startend
      FROM
        filtered_entries AS fe
    )
    SELECT * FROM valid_entries
    WHERE startend IS NOT NULL
    AND NOT EXISTS (
      SELECT 1
      FROM fact_parkingtransaction AS fp
      WHERE fp.eventid_arrival = valid_entries.id
      OR fp.eventid_departure = valid_entries.id
    )
    ORDER BY cardnumber, eventdate';

  OPEN cursorEvent SCROLL FOR EXECUTE cursorQuery;

  LOOP
    FETCH cursorEvent INTO arrived_entry;
    EXIT WHEN NOT FOUND;
    IF arrived_entry.startend = 1 THEN
      FETCH cursorEvent INTO departed_entry;
      IF departed_entry.startend = 2 THEN
        EXECUTE 'INSERT INTO fact_parkingtransaction (
            id,
            entryfacilitykey,
            exitfacilitykey,
            systeminterfacekey,
            manufacturerkey,
            tickettypekey,
            entrydatekey,
            entrytimekey,
            exitdatekey,
            exittimekey,
            entrydevicekey,
            exitdevicekey,
            entrytime,
            exittime,
            duration,
            eventid_arrival,
            eventid_departure,
            cardnumber,
            licenseplate,
            licenseplatekey,
            dateinserted,
            etlsource
          ) VALUES (
            nextval(''fact_parkingtransaction_id_seq''),
            ' || arrived_entry.facilitykey || ',
            ' || departed_entry.facilitykey || ',
            ' || arrived_entry.systeminterfacekey || ',
            ' || arrived_entry.manufacturerkey || ',
            ' || arrived_entry.tickettypekey || ',
            ' || arrived_entry.datekey || ',
            ' || arrived_entry.timekey || ',
            ' || departed_entry.datekey || ',
            ' || departed_entry.timekey || ',
            ' || arrived_entry.devicekey || ',
            ' || departed_entry.devicekey || ',
            ''' || arrived_entry.eventdate || ''',
            ''' || departed_entry.eventdate || ''',
            ' || date_part('epoch', departed_entry.eventdate::timestamp - arrived_entry.eventdate::timestamp) || ',
            ' || arrived_entry.id || ',
            ' || departed_entry.id || ',
            ''' || arrived_entry.cardnumber || ''',
            ''' || arrived_entry.licenseplate || ''',
            ' || arrived_entry.licenseplatekey || ',
            ''' || current_timestamp || ''',
            ''' || arrived_entry.etlsource || '''
          )';
        RAISE NOTICE 'New record inserted into fact_parkingtransaction for card number ''%''', arrived_entry.cardnumber;
      ELSE
        RAISE NOTICE 'Unexpected entry after entry found at event id ''%'' and card number ''%''', arrived_entry.id, arrived_entry.cardnumber;
      END IF;
    ELSE
      RAISE NOTICE 'Unexpected card number or car park change found at event id ''%'' and card number ''%''', arrived_entry.id, arrived_entry.cardnumber;
    END IF;
  END LOOP;
  CLOSE cursorEvent;

  EXECUTE 'SELECT MAX(eventdate) FROM fact_entriesexits WHERE eventdate >= ' || quote_literal(durationLimitDate) INTO maxEventTime;
  durationLimitDate := maxEventTime - interval '1 day' * durationLimitDays;
  EXECUTE 'UPDATE properties SET PROP_VALUE = ' || quote_literal(durationLimitDate) || ' WHERE prop_key = ''DURATION.LIMIT.DATE''';
  RAISE NOTICE 'Updated DURATION.LIMIT.DATE to ''%''', durationLimitDate;
END;
$function$
;

内容的提问来源于stack exchange，提问作者Djabone