获取单行中最大日期的最佳方法

huangapple go评论58阅读模式
英文:

Best way to get the greatest date from a single row

问题

假设以下表格...

创建表格 Dummy_Data
(
ID INT,
TextField VARCHAR(20),
DateField DATE
)

向 Dummy_Data 表格插入数据
VALUES
(1, 'Random Text', '2018-01-04'),
(1, 'Random Text', '2018-02-04'),
(1, 'Random Text', '2018-05-01'),
(2, 'Random Text', '2018-01-14'),
(2, 'Random Text', '2018-06-05'),
(2, 'Random Text', '2018-01-01'),
(2, 'Random Text', '2018-02-01'),
(3, 'Random Text', '2018-09-04')

我想返回以下数据,并添加一个新列 Correcteddate,该列始终应与前一行的日期比较后包含最大日期

(ID, TextField, DateField , Correcteddate
(1, 'Random Text', '2018-01-04','2018-01-04'),
(1, 'Random Text', '2018-02-04','2018-02-04'),
(1, 'Random Text', '2018-05-01','2018-05-01'),
(2, 'Random Text', '2018-01-14','2018-05-01'),
(2, 'Random Text', '2018-06-05','2018-06-05'),
(2, 'Random Text', '2018-01-01','2018-06-05'),
(2, 'Random Text', '2018-02-01','2018-06-05'),
(3, 'Random Text', '2018-09-04','2018-09-04'))

尝试使用 Lead 和 Lag 函数。

英文:

Assume the following table...

CREATE TABLE Dummy_Data
(
ID INT,
TextField VARCHAR(20),
DateField DATE
)

INSERT INTO Dummy_Data (ID, TextField, DateField)
VALUES

   (1, 'Random Text', '2018-01-04'),
   (1, 'Random Text', '2018-02-04'),
   (1, 'Random Text', '2018-05-01'),
   (2, 'Random Text', '2018-01-14'),
   (2, 'Random Text', '2018-06-05'),
   (2, 'Random Text', '2018-01-01'),
   (2, 'Random Text', '2018-02-01'),
   (3, 'Random Text', '2018-09-04')

I would like to return the data as below with a new column corrected date and that should always have the greatest one compared to the previous row

   (ID, TextField, DateField , Correcteddate 
   (1, 'Random Text', '2018-01-04','2018-01-04'),
   (1, 'Random Text', '2018-02-04','2018-02-04'),
   (1, 'Random Text', '2018-05-01','2018-05-01'),
   (2, 'Random Text', '2018-01-14','2018-05-01'),
   (2, 'Random Text', '2018-06-05','2018-06-05'),
   (2, 'Random Text', '2018-01-01','2018-06-05'),
   (2, 'Random Text', '2018-02-01','2018-06-05'),
   (3, 'Random Text', '2018-09-04','2018-09-04')

Tried Lead and Lag functions.

答案1

得分: 2

以下是您要翻译的内容:

首先,您的示例数据似乎不正确。在您的输入中,您有像'2017-01-14'和'2017-05-01'这样的日期,但在期望的输出中似乎变成了'2018-01-14'和'2018-06-05'。对于ID = 3的行也是一样 - '2018-01-04'变成了'2018-09-04'。

根据您的输出,输入看起来更像是:

INSERT INTO Dummy_Data (ID,TextField,DateField) VALUES
(1,'随机文本','2018-01-04'),
(1,'随机文本','2018-02-04'),
(1,'随机文本','2018-05-01'),
(2,'随机文本','2018-01-14'),
(2,'随机文本','2018-06-05'),
(2,'随机文本','2018-01-01'),
(2,'随机文本','2018-02-01'),
(3,'随机文本','2018-09-04');

有了这个,您提到“上一行”,但您没有定义行的排序方式。这些行没有唯一的ID,所以我假设您是按ID和DateField对它们进行排序的,但这与您的输入和输出数据不符。此外,不能保证以这种方式对行进行唯一排序,因为您可能具有相同的ID和DateField值。

另外,请问,是否应该按ID分组行?

如果我们按ID和DateField对行进行排序,并且要查找到目前为止的最大值(似乎您不仅对先前的值感兴趣,还希望获取前两行的最大值,然后将其用作下一行的压缩输出),您可以使用以下查询:

SELECT ID
,TextField
,DateField
,MAX(DateField) OVER (ORDER BY ID, DateField ASC) AS NextDate
FROM Dummy_Data

获取单行中最大日期的最佳方法

英文:

First of all, your sample data seems to be incorrect. In your input you have dates like '2017-01-14' and '2017-05-01', which seems to become '2018-01-14' and '2018-06-05' in the desired output. Same for row with ID = 3 - '2018-01-04' becomes '2018-09-04'.

Based on your output, the input looks more like:

INSERT INTO Dummy_Data (ID, TextField, DateField) VALUES
   (1, 'Random Text', '2018-01-04'),
   (1, 'Random Text', '2018-02-04'),
   (1, 'Random Text', '2018-05-01'),
   (2, 'Random Text', '2018-01-14'),
   (2, 'Random Text', '2018-06-05'),
   (2, 'Random Text', '2018-01-01'),
   (2, 'Random Text', '2018-02-01'),
   (3, 'Random Text', '2018-09-04');

Having this, you are talking about previous row but you are not defining how rows are ordered. There is no unique ID for the rows, so I suppose you are ordering them by ID and DateField, which is not the case with your input and output data. Also, there is no guarantee that the rows will be uniquely order in this way as you may have same DateField values for same ID.

Also, ask yourself, should I group the rows by ID?

And if we are ordering the rows by ID and DateField, and looking for the greatest value till now (it seems that you are not only interesting in the previous value, but you want to get the biggest value for the first two rows, then use it as output for the next row compression), you can use the following:

SELECT ID
      ,TextField
      ,DateField
      ,MAX(DateField) OVER (ORDER BY ID, DateField ASC) AS NextDate
FROM Dummy_Data

获取单行中最大日期的最佳方法

huangapple
  • 本文由 发表于 2023年7月13日 12:10:45
  • 转载请务必保留本文链接:https://go.coder-hub.com/76675860.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定