0

I have a requirement to find the missing days from the start date & end date of a particular ID and fill the missed date by taking the average of the preceding and succeeding rows .

For example: In the input dataset "2023-01-23" this date is missing and I would like to add this date but the Value column should contain the AVERAGE of "2023-01-24" & "2023-01-22" this dates. Any idea how i can get this done in Postgres (V 12.3)?

Expected output

ID.     Value       Date
    8445    0.0000      "2023-01-25"
    8445    0.0000      "2023-01-24"
    8445    0.0000      "2023-01-23". --Value is the average of above and below row
    8445    0.0000      "2023-01-22"
    8445    0.0000      "2023-01-21" --Value is the average of above and below row
    8445    0.0000      "2023-01-20"

Input Dataset

ID.     Value       Date
    8445    0.0000      "2023-01-25"
    8445    0.0000      "2023-01-24"
    8445    0.0000      "2023-01-22"
    8445    0.0000      "2023-01-20"
    8445    0.0000      "2023-01-19"
    8445    0.0000      "2022-12-29"
    8445    0.0000      "2022-12-27"
    8445    0.0000      "2022-12-26"
    8445    0.0000      "2022-12-25"
    8445    0.0000      "2022-12-23"
    8445    0.0000      "2022-12-22"
    8445    0.0000      "2022-12-21"
    8445    0.0000      "2022-12-20"
    8445    0.0000      "2022-12-18"
    8445    0.0000      "2022-12-16"
    8445    0.0000      "2022-12-15"
    8445    0.0000      "2022-12-14"
    8445    0.0000      "2022-12-13"
    8445    0.0000      "2022-12-11"
    8445    0.0000      "2022-12-10"
    8445    0.0000      "2022-12-09"
    8445    111.0000    "2022-12-07"
    8445    624.0000    "2022-12-06"
    8445    1010.0000   "2022-12-05"
    8445    1305.0000   "2022-12-04"
    8445    1479.0000   "2022-12-02"
    8445    1708.0000   "2022-12-01"
    8445    1911.0000   "2022-11-30"
    8445    2264.0000   "2022-11-29"
    8445    2675.0000   "2022-11-28"
    8445    3347.0000   "2022-11-27"
    8445    3895.0000   "2022-11-26"
    8445    7873.0000   "2022-11-24"
    8445    8486.0000   "2022-11-22"
    8445    8725.0000   "2022-11-20"
    8445    9072.0000   "2022-11-19"
    8445    9356.0000   "2022-11-18"
    8445    9986.0000   "2022-11-17"
    8445    10178.0000  "2022-11-16"
    8445    10507.0000  "2022-11-15"
    8445    10771.0000  "2022-11-14"
    8445    11096.0000  "2022-11-13"
    8445    11452.0000  "2022-11-12"
    8445    11677.0000  "2022-11-11"
    8445    11966.0000  "2022-11-10"
    8445    12229.0000  "2022-11-09"
    8445    13128.0000  "2022-11-08"
    8445    13488.0000  "2022-11-07"
    8445    14406.0000  "2022-11-05"
    8445    14737.0000  "2022-11-03"
    8445    15045.0000  "2022-11-02"
    8445    15360.0000  "2022-11-01"
    8445    15822.0000  "2022-10-31"
    8445    16166.0000  "2022-10-30"
    8445    16477.0000  "2022-10-29"
    8445    16697.0000  "2022-10-28"
    8445    16973.0000  "2022-10-27"
    8445    17285.0000  "2022-10-26"
    8445    17585.0000  "2022-10-25"
    8445    17879.0000  "2022-10-24"
    8445    18253.0000  "2022-10-23"
    8445    18614.0000  "2022-10-22"
    8445    18829.0000  "2022-10-21"
    8445    19169.0000  "2022-10-20"
    8445    19446.0000  "2022-10-19"
    8445    11286.0000  "2022-10-18"
    8445    11650.0000  "2022-10-17"
    8445    2975.0000   "2022-10-16"
    8445    3379.0000   "2022-10-15"
    8445    1263.0000   "2022-10-14"
    8445    267.0000    "2022-10-12"
    8445    944.0000    "2022-10-10"
    8445    1254.0000   "2022-10-09"
    8445    1459.0000   "2022-10-08"
    8445    156.0000    "2022-10-07"
    8445    469.0000    "2022-10-06"
    8445    1076.0000   "2022-10-04"
    8445    1447.0000   "2022-10-03"
    8445    4856.0000   "2022-09-22"
    8445    6019.0000   "2022-09-20"
    8445    7027.0000   "2022-09-17"
    8445    5248.0000   "2022-09-16"
    8445    0.0000      "2022-09-14"
Sandeep
  • 671
  • 2
  • 7
  • 30
  • Why do you need: "but the Value column should contain the AVERAGE ...." ?? – Luuk Jan 31 '23 at 11:56
  • @Luuk this is a fallback we had to implement , if there are no values updated in the database for a specific day.Seems not to be correct, but it will be suffice.Any idea how we can do it? – Sandeep Jan 31 '23 at 11:59
  • You can calculate with dates, see: `elect ('2023-01-01'::date + (generate_series::char(2)||' DAY')::interval)::date from generate_series(0,30);`, which will get you all the date from January 2023 – Luuk Jan 31 '23 at 12:04
  • @LuukThis is just a generate series of the dates right? I would like to have the missing dates added and update the value column of the missed date as the average of above and below rows. – Sandeep Jan 31 '23 at 12:08
  • Calculating the average of two dates has been answered here: https://stackoverflow.com/a/32732713/724039 – Luuk Jan 31 '23 at 12:24
  • Would [adding one day](https://stackoverflow.com/a/63142013/724039) not be easier ? – Luuk Jan 31 '23 at 12:26

0 Answers0