如何在SQL中创建一个唯一/不同的id

Question

我有一个SQL代码，有3列。(1) UUID (2) timestamp 1 (3) timestamp 2。我试图创建一个列表，它采用唯一的uuid和时间戳1的第一次出现。我用了不同的函数，但它不起作用。谁能帮我理解一下正确的代码是什么?

感谢

with raw_data as ( select UUID, cast(datestring as timetamp) as date1 date2 from XYZ )
Select distinct UUID, date1, date2
from raw_data

raw_data是

---

ID # # # # Date1 (string) # # # # date2(时间戳)

1. 2021-07-05          2012-07-05
   

2. 2021-07-05           2012-12-12
   

3. 2021-07-12             2018-04-07
   

4. 2021-07-19              2012-12-12
   

---

期望输出

1. 2021-07-05。2012-07-05
2. 2021-07-05 2012-12-12
3. 2021-07-12 2018-04-07

Answer 1

下表使用组by和MIN来获得timestamp1的第一次出现

SELECT
UUID,
MIN(timestamp1) as first_occurrence_of_timestamp_1
FROM
mytable
GROUP BY
UUID

编辑1

作为对时间戳转换的响应，我在hive上运行了以下命令，得到了以下结果:

WITH XYZ AS (
SELECT 1 as UUID, '2021-07-05' as datestring, '2012-07-05' as date2 
UNION ALL
SELECT 2, '2021-07-05', '2012-12-12'
UNION ALL
SELECT 3, '2021-07-12', '2018-04-07'
UNION ALL
SELECT 3, '2021-07-19', '2012-12-12'
),
raw_data AS (
SELECT
UUID,
CAST(datestring as TIMESTAMP) date1,
date2
FROM XYZ
)
SELECT
UUID,
MIN(date1) as first_occurrence_of_timestamp_1,
MIN(date2) as earliest_date_2
FROM 
raw_data
GROUP BY 
UUID

输出:

tbody> <<tr>2

uuid	first_ce_of_timestamp_1	earest_date_2
1	2021-07-05 00:00:00.0	2012-07-05
	2021-07-05 00:00:00.0	2012-12-12
3	2021-07-12 00:00:00.0	2012-12-12