将CSV读取到Spring Boot应用程序时，如何将数据表列从累积转换为差异? [英] How do I transform a data table column from cumulative to difference when reading CSV into spring boot application?

问题描述

我在一个表中有数据

   date  | city | Cumulative total 
---------------------------------
1/1/2020 | NYC  |    10
1/2/2020 | NYC  |    15
1/3/2020 | NYC  |    31
1/4/2020 | NYC  |    36
1/5/2020 | NYC  |    55
 .
 .  // more data for NYC continued
 .
1/1/2020 | BER  |    1
1/2/2020 | BER  |    5
1/3/2020 | BER  |    13
1/4/2020 | BER  |    42
1/5/2020 | BER  |    45
 .
 .  // more data for BER continued
 .

我希望这些数据不保存 cumulative ，而是保存 difference .基本上，我想从前一天减去第二天，以确保城市匹配.

I want this data to not hold the cumulative, but rather hold the difference. Basically I want to subtract the next day from the day before it, making sure that the cities match up.

   date  | city | Cumulative total 
---------------------------------
1/1/2020 | NYC  |    10
1/2/2020 | NYC  |    5
1/3/2020 | NYC  |    16
1/4/2020 | NYC  |    5
1/5/2020 | NYC  |    19
 .
 .  // more data for NYC continued
 .
1/1/2020 | BER  |    1
1/2/2020 | BER  |    4
1/3/2020 | BER  |    8
1/4/2020 | BER  |    29
1/5/2020 | BER  |    3
 .
 .  // more data for BER continued
 .

我将数据存储在CSV中，并且将其加载到Spring Boot应用程序的数据库中.但是，Spring Boot应用程序需要的是差异，而不是累加的差异.我该如何正确转换这些数据

I have the data within a CSV and am to load it into a database for a spring boot application. However, the spring boot application needs the difference, not the cumulative. How can I properly transform this data either

1. 从CSV读取数据后是否在数据库内?

1. Within the database upon reading the data from the CSV?

通过在 JpaRepository 中编写一个特殊的查询，以便我的POJO作为转换后的数据返回?

By writing a special query within the JpaRepository so that my POJO's come back as the transformed data?

我不知道如何实现上述任何一个，但是它们是我的想法.我要求有人帮助我看看处理这种情况的最行业标准"是什么.也许有比我建议的更好的方法.

I have no idea how to implement either of the previous, but they are my ideas for what to do. I ask that someone help me see what the most "industry standard" way to handle this situation is. Maybe there is a better way than what I proposed.

谢谢！

推荐答案

如果您的数据库支持窗口函数，对于 lag()来说，这是一项简单的任务，它使您可以访问上一个行，指定了分区和 order by 规范:

If your database supports window functions, this is an easy task for lag(), which lets you access any column on the previous row, given a partition and order by specification:

select 
    t.*,
    cumulative 
        - lag(cumulative, 1, 0) over(partition by city order by date) as difference
from mytable t

并非所有数据库都支持 lag()的3参数形式，在这种情况下，您可以执行以下操作:

Not all databases support the 3-argument form of lag(), in which case you can do:

select
    t.*,
    coalesce(
        cumulative - lag(cumulative) over(partition by city order by date),
        cumulative
    ) difference
from mytable t

这篇关于将CSV读取到Spring Boot应用程序时，如何将数据表列从累积转换为差异?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！