android - Android 房间数据库查询是否支持 row_number？

Question

我试图让以下 sqlite 查询在我的 android 房间数据库中工作，但它提示有几个错误。

@Query("with cte as (\n" +
        "  select t.exercise_name,t.band, t.reps, t.weight\n" +
        "  from ( \n" +
        "    select *,\n"+      "" +
        "      row_number() over (partition by band order by reps desc) rnreps,\n" +
        "      row_number() over (partition by band, reps order by weight desc) rnweight" +
        "    from tracked_exercises\n" +
        "  ) t  \n" +
        "  where exercise_name = :name\n" +
        ")  \n" +
        "select * from cte c\n" +
        "where not exists (\n" +
        "  select 1 from cte\n" +
        "  where band = c.band and reps > c.reps and weight >= c.weight\n" +
        ")  \n")
List<TrackedExercise> getPersonalRecords(String name);

1 就在分区前面的支架前 "<compound operator>, FROM, GROUP, LIMIT, ORDER, WHERE or comma expected, got '('"
另一个当我尝试编译 "error: no viable alternative at input 'with cte as...."
我已经设法在 fiddle here 上获得相同的查询

是 row_number() over (partition' Room 不支持子句还是我遗漏了什么？

编辑:
刚刚找到这个 Android Room + Window Functions看起来这个功能只在 SQLite 3.25 和更高版本的 Android 中可用，最高只能到 3.19。如果有人对如何在不使用 Windows 函数的情况下创建执行相同操作的查询有任何想法，我仍然很感兴趣

Answer 1

我想我使用 2 个 CTE 找到了解决问题的方法，然后加入结果

@Query("-- gets the max reps at each set of exercise_name, band, weight\n" +
        "with repsCTE as (\n" +
        "  select *,max(reps) reps\n" +
        "  from tracked_exercises\n" +
        "  where exercise_name = :name\n" +
        "  group by exercise_name, band, weight ), \n" +
        "\n" +
        "-- gets the max weight at each set of exercise_name, band, reps\n" +
        "weightCTE as (\n" +
        "  select *,max(weight) weight\n" +
        "  from tracked_exercises\n" +
        "  where exercise_name = :name\n" +
        "  group by exercise_name, band, reps ) \n" +
        "\n" +
        "select * \n" +
        "from repsCTE r join weightCTE w\n" +
        "on r.id = w.id\n" +
        "order by band,reps" )
List<TrackedExercise> getPersonalRecords(String name);

Fiddle