SQL获取"when a status change"日期(考虑到相同的状态可以出现很多次)

Question

我尝试解决在"apache Spark内置SQL api">

如果资源密集型较小，则优先使用pyspark-sql。上下文:我有一组文件按照计划打印的文件(并非所有文件都每天打印)和打印的部件(即printed_id))接收一个状态(又名printed_status))可以是A, B, c

这些事件被记录在一个表中，就像(但是有一百万条记录):

|  file | printed_id | printed_date | printed_status |
|-------|------------|--------------|----------------|
| file1 |        100 |   2020-07-01 |              A | <<
| file1 |        190 |   2020-07-02 |              A |
| file1 |        302 |   2020-07-06 |              B | <<
| file1 |        343 |   2020-07-09 |              B |
| file1 |        464 |   2020-07-10 |              A | <<
| file1 |        523 |   2020-07-20 |              A |
| file1 |        567 |   2020-07-25 |              A |
| file1 |        578 |   2020-07-28 |              C | <<
| file1 |        670 |   2020-07-29 |              C |
| file1 |        691 |   2020-07-30 |              C |

我想只保留出现状态的行(不管值是什么)。它对应于标有<<在上表中。(我的最终目标是计算每次状态变化之间的时间跨度)。

我期望的输出应该是(包括<<行):

|  file | printed_status | status_first_occurence_on |
|-------|----------------|---------------------------|
| file1 |              A |                2020-07-01 |
| file1 |              B |                2020-07-06 |
| file1 |              A |                2020-07-10 | <<
| file1 |              C |                2020-07-28 |

而不是用SELECT file, printed_status, MIN(printed_date) AS status_first_occurence_on FROM myTable GROUP BY file, printed_status得到的输出:

|  file | printed_status | status_first_occurence_on |
|-------|----------------|---------------------------|
| file1 |              A |                2020-07-01 |
| file1 |              B |                2020-07-06 |
| file1 |              C |                2020-07-28 |

我读了sql-first-date-for- first-occurrence-of-a-value，它很接近，但并不完全适用于我的情况。

当我在非常庞大的数据集上工作时我很担心计算成本。我担心在同一个大表的两次迭代上应用Row_count()函数然后将它们连接起来将花费大量的计算资源。但是我可能错了，因为我不知道计算成本是如何工作的。

谢谢你的帮助和回答。

Answer 1

您可以使用lag来比较当前行与前一行的打印状态。如果它们是相同的，您可以标记它，然后过滤未标记的行。

select file, printed_id, printed_date, printed_status
from (
select *,
printed_status <=> lag(printed_status) 
over(partition by file order by printed_date) flag
from mytable
) where not flag;
+-----+----------+------------+--------------+
| file|printed_id|printed_date|printed_status|
+-----+----------+------------+--------------+
|file1|       100|  2020-07-01|             A|
|file1|       302|  2020-07-06|             B|
|file1|       464|  2020-07-10|             A|
|file1|       578|  2020-07-28|             C|
+-----+----------+------------+--------------+

(<=>)是一个null安全的相等运算符，如果比较的项之一为null，则返回false。当lag为每个分区的第一行返回null时，这很有用，因为我们希望该标志为false而不是null。