preceeding test case

[pyramation]

• preceding

WITH timestamp_measurement AS (SELECT count(t1.id) AS count_num
                                    , date_trunc('month', t1.start_date) AS timestamp
                               FROM trip AS t1
                               GROUP BY timestamp)

  SELECT t2.timestamp AS timestamp
       , avg(t2.count_num) OVER (ORDER BY t2.timestamp ASC RANGE BETWEEN '3 months' PRECEDING AND CURRENT ROW) AS moving_count_num
  FROM timestamp_measurement AS t2

{
"query": {
  "version": 130002,
  "stmts": [
    {
      "stmt": {
        "SelectStmt": {
          "targetList": [
            {
              "ResTarget": {
                "name": "timestamp",
                "val": {
                  "ColumnRef": {
                    "fields": [
                      {
                        "String": {
                          "str": "t2"
                        }
                      },
                      {
                        "String": {
                          "str": "timestamp"
                        }
                      }
                    ],
                    "location": 163
                  }
                },
                "location": 163
              }
            },
            {
              "ResTarget": {
                "name": "moving_count_num",
                "val": {
                  "FuncCall": {
                    "funcname": [
                      {
                        "String": {
                          "str": "avg"
                        }
                      }
                    ],
                    "args": [
                      {
                        "ColumnRef": {
                          "fields": [
                            {
                              "String": {
                                "str": "t2"
                              }
                            },
                            {
                              "String": {
                                "str": "count_num"
                              }
                            }
                          ],
                          "location": 195
                        }
                      }
                    ],
                    "over": {
                      "orderClause": [
                        {
                          "SortBy": {
                            "node": {
                              "ColumnRef": {
                                "fields": [
                                  {
                                    "String": {
                                      "str": "t2"
                                    }
                                  },
                                  {
                                    "String": {
                                      "str": "timestamp"
                                    }
                                  }
                                ],
                                "location": 224
                              }
                            },
                            "sortby_dir": "SORTBY_ASC",
                            "sortby_nulls": "SORTBY_NULLS_DEFAULT",
                            "location": -1
                          }
                        }
                      ],
                      "frameOptions": 3091,
                      "startOffset": {
                        "A_Const": {
                          "val": {
                            "String": {
                              "str": "3 months"
                            }
                          },
                          "location": 255
                        }
                      },
                      "location": 214
                    },
                    "location": 191
                  }
                },
                "location": 191
              }
            }
          ],
          "fromClause": [
            {
              "RangeVar": {
                "relname": "timestamp_measurement",
                "inh": true,
                "relpersistence": "p",
                "alias": {
                  "aliasname": "t2"
                },
                "location": 318
              }
            }
          ],
          "limitOption": "LIMIT_OPTION_DEFAULT",
          "withClause": {
            "ctes": [
              {
                "CommonTableExpr": {
                  "ctename": "timestamp_measurement",
                  "ctematerialized": "CTEMaterializeDefault",
                  "ctequery": {
                    "SelectStmt": {
                      "targetList": [
                        {
                          "ResTarget": {
                            "name": "count_num",
                            "val": {
                              "FuncCall": {
                                "funcname": [
                                  {
                                    "String": {
                                      "str": "count"
                                    }
                                  }
                                ],
                                "args": [
                                  {
                                    "ColumnRef": {
                                      "fields": [
                                        {
                                          "String": {
                                            "str": "t1"
                                          }
                                        },
                                        {
                                          "String": {
                                            "str": "id"
                                          }
                                        }
                                      ],
                                      "location": 47
                                    }
                                  }
                                ],
                                "location": 41
                              }
                            },
                            "location": 41
                          }
                        },
                        {
                          "ResTarget": {
                            "name": "timestamp",
                            "val": {
                              "FuncCall": {
                                "funcname": [
                                  {
                                    "String": {
                                      "str": "date_trunc"
                                    }
                                  }
                                ],
                                "args": [
                                  {
                                    "A_Const": {
                                      "val": {
                                        "String": {
                                          "str": "month"
                                        }
                                      },
                                      "location": 82
                                    }
                                  },
                                  {
                                    "ColumnRef": {
                                      "fields": [
                                        {
                                          "String": {
                                            "str": "t1"
                                          }
                                        },
                                        {
                                          "String": {
                                            "str": "start_date"
                                          }
                                        }
                                      ],
                                      "location": 91
                                    }
                                  }
                                ],
                                "location": 71
                              }
                            },
                            "location": 71
                          }
                        }
                      ],
                      "fromClause": [
                        {
                          "RangeVar": {
                            "relname": "trip",
                            "inh": true,
                            "relpersistence": "p",
                            "alias": {
                              "aliasname": "t1"
                            },
                            "location": 124
                          }
                        }
                      ],
                      "groupClause": [
                        {
                          "ColumnRef": {
                            "fields": [
                              {
                                "String": {
                                  "str": "timestamp"
                                }
                              }
                            ],
                            "location": 144
                          }
                        }
                      ],
                      "limitOption": "LIMIT_OPTION_DEFAULT",
                      "op": "SETOP_NONE"
                    }
                  },
                  "location": 8
                }
              }
            ],
            "location": 3
          },
          "op": "SETOP_NONE"
        }
      }
    }
  ]
},
"stderr": ""
}

[pyramation]

fixed with https://github.com/pganalyze/libpg_query/blob/442b1748d06364ecd3779bc558899176c02efaf0/src/postgres/include/nodes/parsenodes.h#L505-L522