时间戳

首页 > 解决方案 > 插入 MATCH_RECOGNIZE 选择 Oracle 的第一行和最后一行

database - 插入 MATCH_RECOGNIZE 选择 Oracle 的第一行和最后一行

问题描述

我不太擅长 sql，我需要你的帮助。

我在 Oracle DB 中有一个包含示例数据的表，如下所示：

| DATE_TIME                 | TYPE | LAT   | LON   | MSG   |
| :------------------------ | :--- | :---- | :---- | :---- |
| 12.11.20 10:04.00.0000000 | A    | 10.03 | 11.15 |       |
| 12.11.20 10:05.00.0000000 | A    | 10.04 | 11.14 |       |
| 12.11.20 10:06.00.0000000 | A    | 10.05 | 11.13 |       |
| 12.11.20 11:18.00.0000000 | B    | 20.45 | 40.58 |       |
| 12.11.20 11:19.00.0000000 | B    | 20.46 | 40.59 |       |
| 12.11.20 11:20.00.0000000 | B    | 20.47 | 40.60 |       |

我有一个选择，它给了我START_TIME、END_TIME、TYPE、START_LAT、START_LON、END_LAT、END_LON、START_MSG、END_MSG：

SELECT 
  TO_CHAR(START_TIME, 'DD.MM.YYYY HH24:MI') AS START_TIME,
  TO_CHAR(END_TIME, 'DD.MM.YYYY HH24:MI') AS END_TIME,
  TYPE,
  START_LAT,
  START_LON,
  END_LAT,
  END_LON,
  START_MSG,
  END_MSG
FROM TABLE_1
  MATCH_RECOGNIZE (
    PARTITION BY TYPE
    ORDER BY DATE_TIME
    MEASURES
      FIRST(DATE_TIME) AS START_TIME,
      LAST(DATE_TIME) AS END_TIME,
      FIRST(LAT) AS START_LAT,
      FIRST(LON) AS START_LON,
      LAST(LAT) AS END_LAT,
      LAST(LON) AS END_LON,
      FIRST(MSG) AS START_MSG,
      LAST(MSG) AS END_MSG
    ONE ROW PER MATCH
    PATTERN ( adjacent_minutes * last_minute )
    DEFINE adjacent_minutes AS NEXT(DATE_TIME) <= LAST(DATE_TIME) + INTERVAL '5' MINUTE
  );

此选择的输出：

| START_TIME     | END_TIME       | TYPE | START_LAT | START_LON | END_LAT | END_LON | START_MSG | END_MSG |
| :------------- | :------------- | :--- | :-------- | :-------- | :------ | :------ | :-------- | :------ |
| 12.11.20 10:04 | 12.11.20 10:06 | A    | 10.03     | 11.15     | 10.05   | 11.13   |           |         |
| 12.11.20 11:18 | 12.11.20 11:20 | B    | 20.45     | 40.58     | 20.47   | 40.60   |           |         |

现在，当例如START_LAT介于 10 和 11 之间并且START_LON介于 11 和 12 之间时，我需要将值为“国家 xy”的数据插入 START_MSG 列。对于END_MSG也是如此。

我怎样才能做到这一点？

抱歉我的英语不好；）。

非常感谢您！

标签： databaseoraclesql-insertmatch-recognize

解决方案

您可以使用CASE WHEN如下表达式：

SELECT ...
  ....
  END_LAT,
  END_LON,
  CASE WHEN START_LAT BETWEEN 10 AND 11 AND START_LON BETWEEN 11 AND 12 
       THEN 'country xy' 
       ELSE START_MSG 
  END AS START_MSG,
  CASE WHEN START_LAT BETWEEN 10 AND 11 AND START_LON BETWEEN 11 AND 12 
       THEN 'country xy' 
       ELSE END_MSG 
  END AS END_MSG
FROM TABLE_1
  MATCH_RECOGNIZE (
    ...

或在子查询中使用您的整个查询，在外部查询中将其CASE..WHEN用于这两列

推荐阅读