How to use postgresql statement to realize data accumulation

suppose there are two tables now. The first table, called prepay_card, represents prepaid cards, and the second table represents transactions of prepaid cards represented by prepay_card_transition

.

the structure of our first table, prepay_card, is as follows:

< table > < thead > < tr > < th > Column < / th > < th > Type < / th > < th > Collation < / th > < th > Nullable < / th > < th > Default < / th > < / tr > < / thead > < tbody > < tr > < td > prepay_card_id < / td > < td > integer < / td > < td > < / td > < td > not null < / td > < td > generated by default as identity < / td > < / tr > < tr > < td > total < / td > < td > numeric (129.2) < / td > < td > < / td > < td > not null < / td > < td > < / td > < / tr > < tr > < td > shopper_id < / td > < td > integer < / td > < td > < / td > < td > not null < / td > < td > < / td > < / tr > < tr > < td > created_at < / td > < td > timestamp with time zone < / td > < td > < / td > < td > not null < / td > < td > CURRENT_TIMESTAMP < / td > < / tr > < / tbody > < / table >

the structure of our second table is as follows:

< table > < thead > < tr > < th > Column < / th > < th > Type < / th > < th > Collation < / th > < th > Nullable < / th > < th > Default < / th > < / tr > < / thead > < tbody > < tr > < td > id < / td > < td > integer < / td > < td > < / td > < td > not null < / td > < td > generated by default as identity < / td > < / tr > < tr > < td > prepay_card_id < / td > < td > integer < / td > < td > < / td > < td > not null < / td > < td > < / td > < / tr > < tr > < td > shopper_id < / td > < td > integer < / td > < td > < / td > < td > not null < / td > < td > < / td > < / tr > < tr > < td > total < / td > < td > numeric (129.2) < / td > < td > < / td > < td > not null < / td > < td > < / td > < / tr > < tr > < td > created_at < / td > < td > timestamp with time zone < / td > < td > < / td > < td > not null < / td > < td > CURRENT_TIMESTAMP < / td > < / tr > < / tbody > < / table >

ask how you want to combine the two tables to export a new table that can present the following data.

< table > < thead > < tr > < th > time | Card number | Card face value | sales amount | remaining amount | processing type < / th > < / tr > < / thead > < / table >

for example, if a user with shopper_id 1 buys a prepaid card, there will be a data entry in the prepay_ card table

< table > < thead > < tr > < th > prepay_card_id < / th > < th > shopper_id < / th > < th > total < / th > < th > created_at < / th > < / tr > < / thead > < tbody > < tr > < td > 1 < / td > < td > 1 < / td > < td > 1000.00 < / td > < td > 2018-12-19 1014 1414 3295861408 < / td > < td > < / td > < / tr > < / tbody > < / table >

then he has two more purchases, and there are two items of data in the prepay_card_ substitution table

< table > < thead > < tr > < th > id < / th > < th > prepay_card_id < / th > < th > shopper_id < / th > < th > total < / th > < th > created_at < / th > < / tr > < / thead > < tbody > < tr > < td > 1 < / td > < td > 1 < / td > < td > 1 < / td > < td > 100.00 < / td > < td > 2018-12-19 1111MUR 17.3295861408 < / td > < td > < / td > < / tr > < tr > < td > 2 < / td > < td > 1 < / td > < td > 1 < / td > < td > 100.00 < / td > < td > 2018-12-19 12-34 motto 17.32958658-08 < / td > < td > < / td > < / tr > < / tbody > < / table >

the desired data should be like this:

< table > < thead > < tr > < th > time < / th > < th > Card number < / th > < th > Card amount < / th > < th > sales amount < / th > < th > remaining amount < / th > < th > processing type < / th > < / tr > < / thead > < tbody > < tr > < td > 2018-12-19 1014 1414 3295861408 < / td > < td > 1 < / td > < td > 1000 < / td > < td > 0 < / td > < td > 1000 < / td > < td > purchase cards < / td > < / tr > < tr > < td > 2018-12-19 1111MUR 17.3295861408 < / td > < td > 1 < / td > < td > 1000 < / td > < td > 100 < / td > < td > 900 < / td > < td > consumption < / td > < / tr > < tr > < td > 2018-12-19 12-34 motto 17.32958658-08 < / td > < td > 1 < / td > < td > 1000 < / td > < td > 100 < / td > < td > 800 < / td > < td > consumption < / td > < / tr > < / tbody > < / table >

how do you use sql to implement such a calculation? Or to put it this way, the difficulty is actually the calculation of the remaining amount, if the remaining amount is calculated.

Sql-statement postgresql
Mar.05,2022 
WITH data AS (
SELECT created_at, id AS card_id, total, total AS remaining_total FROM prepay_card
UNION ALL
SELECT pct.created_at, pct.prepay_card_id AS card_id, pc.total, -pct.total AS remaining_total
FROM prepay_card_transaction pct 
INNER JOIN prepay_card pc ON pct.prepay_card_id = pc.id
)
SELECT created_at, card_id, total,sum(remaining_total) OVER (PARTITION BY card_id ORDER BY created_at) AS remaining_total_all FROM data
ORDER BY card_id, created_at ;

Previous: The swiper component in Mini Program, and the video component in it, exits the full screen immediately when it is full screen.

Next: JS sorts by a key value of the object

css mysql arrays josn react html typescript webpack npm sass R objective-c .net sql-server jquery python-3.x angularjs django angular excel regex iphone ajax linux xml pandas vba spring database wordpress string wpf xcode windows bash postgresql oracle multithreading eclipse list firebase algorithm macos forms image scala visual-studio azure bootstrap spring-boot react-native python-2.7 docker performance function winforms matlab powershell apache dataframe api sqlite numpy rest shell selenium flutter dart maven loops qt swing android-studio csv express file class tensorflow sorting codeigniter perl
© 2021 CodesHelper
Advertising Contact us About us
Menu