python中SQL表最小最大值的提取

Question

我有一个数据库，包含以下信息：

+----+------+-----+-----+------+------+-----------+    
| id | time | lat | lon | dep  | dest |  reg      |    
+----+------+-----+-----+------+------+-----------+    
| a  |    1 |  10 |  20 | home | work | alpha     |    
| a  |    6 |  11 |  21 | home | work | alpha     |    
| a  |   11 |  12 |  22 | home | work | alpha     |    
| b  |    2 |  70 |  80 | home | cine | beta      |    
| b  |    8 |  70 |  85 | home | cine | beta      |    
| b  |   13 |  70 |  90 | home | cine | beta      |    
+----+------+-----+-----+------+------+-----------+    

是否可以提取以下信息：

+----+------+------+----------+----------+----------+----------+------+------+--------+    
| id | tmin | tmax | lat_tmin | lon_tmin | lat_tmax | lon_tmax | dep  | dest |   reg  |    
+----+------+------+----------+----------+----------+----------+------+------+--------+    
| a  |    1 |   11 |       10 |       20 |       12 |       22 | home | work | alpha  |    
| b  |    2 |   13 |       70 |       80 |       70 |       90 | home | cine | beta   |    
+----+------+------+----------+----------+----------+----------+------+------+--------+

如果dep&dest是不同的，您将如何选择它们？你知道吗

厚度

Answer 1

select min(t.time) as tmin, max(t.time) as tmax, (...) from table_name t group by t.dest

（…）对其他列重复

Answer 2

您可以使用窗口函数：

SELECT     t0.id,
           t0.time as     tmin, t1.time as     tmax,
           t0.lat  as lat_tmin, t1.lat  as lat_tmax,
           t0.lon  as lon_tmin, t1.lon  as lon_tmax,
           t0.dep,
           t0.dest,
           t0.reg
FROM       ( SELECT *, 
                    row_number() over (partition by id order by time asc) as rn
             FROM   t) AS t0
INNER JOIN ( SELECT *, 
                    row_number() over (partition by id order by time desc) as rn
             FROM   t) AS t1
        ON t0.id = t1.id
WHERE      t0.rn = 1 
       AND t1.rn = 1

当按id，time排序时，这将返回每个id的第一行和最后一行的数据。你知道吗

dep、dest和reg的值仅取自第一行（每个id）。你知道吗

如果您还想为相同的id设置不同的dep或dest值，那么只需在partition by子句中添加这些值。这一切都取决于您在这种情况下期望的输出：

SELECT     t0.id,
           t0.time as     tmin, t1.time as     tmax,
           t0.lat  as lat_tmin, t1.lat  as lat_tmax,
           t0.lon  as lon_tmin, t1.lon  as lon_tmax,
           t0.dep,
           t0.dest,
           t0.reg           
FROM       ( SELECT *, 
                    row_number() over (partition by id, dep, dest, reg 
                                       order by time asc) as rn
             FROM   t) AS t0
INNER JOIN ( SELECT *, 
                    row_number() over (partition by id, dep, dest, reg 
                                       order by time desc) as rn
             FROM   t) AS t1
        ON t0.id = t1.id
WHERE      t0.rn = 1 
       AND t1.rn = 1

请考虑为列time使用不同的名称，因为this remark in the documentation：

The following keywords could be reserved in future releases of SQL Server as new features are implemented. Consider avoiding the use of these words as identifiers.

... TIME ...