时间戳

首页 > 解决方案 > android 的 SQLite 中的窗口函数

sql - android 的 SQLite 中的窗口函数

问题描述

我正在努力使用 android 尝试使用窗口功能。我有一个数据库，其中有一个 created_at 字段和一个 test 字段的条目，当我尝试使用该代码为每个测试获取创建的最大值时（我的代码在 Kotlin 中，但我猜 java 代码将是相同的)

db.rawQuery("SELECT MAX(created_at) OVER (PARTITION BY test ORDER BY created_at) FROM test_result;", arrayOf()).close()

我的应用程序崩溃了，但是使用该代码

db.rawQuery("SELECT MAX(created_at) FROM test_result;", arrayOf()).close()

一切正常

显然在那个用例中，我可以做一个子查询并加入最大值表，但实际上我要做的需要在每个测试的最后 4 个条目中获取平均值，所以我真的需要窗口函数和我很困惑它们不能在 android 中工作，因为它们在普通 SQLite 中工作

此外，该错误完全没用

09-03 07:58:28.627 10723 10723 E AndroidRuntime: java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.memorizer/com.example.memorizer.TestActivity}: android.database.sqlite.SQLiteException: near "(": syntax error (code 1): , while compiling: SELECT MAX(created_at) OVER (PARTITION BY test ORDER BY created_at) FROM test_result;
09-03 07:58:28.627 10723 10723 E AndroidRuntime: #################################################################
09-03 07:58:28.627 10723 10723 E AndroidRuntime: Error Code : 1 (SQLITE_ERROR)
09-03 07:58:28.627 10723 10723 E AndroidRuntime: Caused By : SQL(query) error or missing database.
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    (near "(": syntax error (code 1): , while compiling: SELECT MAX(created_at) OVER (PARTITION BY test ORDER BY created_at) FROM test_result;)
09-03 07:58:28.627 10723 10723 E AndroidRuntime: #################################################################
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2957)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3032)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.app.ActivityThread.-wrap11(Unknown Source:0)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1696)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.os.Handler.dispatchMessage(Handler.java:105)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.os.Looper.loop(Looper.java:164)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.app.ActivityThread.main(ActivityThread.java:6944)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at java.lang.reflect.Method.invoke(Native Method)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at com.android.internal.os.Zygote$MethodAndArgsCaller.run(Zygote.java:327)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:1374)
09-03 07:58:28.627 10723 10723 E AndroidRuntime: Caused by: android.database.sqlite.SQLiteException: near "(": syntax error (code 1): , while compiling: SELECT MAX(created_at) OVER (PARTITION BY test ORDER BY created_at) FROM test_result;
09-03 07:58:28.627 10723 10723 E AndroidRuntime: #################################################################
09-03 07:58:28.627 10723 10723 E AndroidRuntime: Error Code : 1 (SQLITE_ERROR)
09-03 07:58:28.627 10723 10723 E AndroidRuntime: Caused By : SQL(query) error or missing database.
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    (near "(": syntax error (code 1): , while compiling: SELECT MAX(created_at) OVER (PARTITION BY test ORDER BY created_at) FROM test_result;)
09-03 07:58:28.627 10723 10723 E AndroidRuntime: #################################################################
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1096)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:661)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:59)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1746)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1685)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at com.example.memorizer.TestActivity.getData(TestActivity.kt:202)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at com.example.memorizer.TestActivity.onCreate(TestActivity.kt:184)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.app.Activity.performCreate(Activity.java:7183)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1220)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2910)
09-03 07:58:28.627 10723 10723 E AndroidRuntime:    ... 9 more

我的 sqlite 版本是 3.18.2

谢谢你的时间

编辑：问题是我手机上的 sqlite 版本，3.25.0 是第一个引入窗口功能的版本，我的版本比那个版本更旧

标签： sqlsqlitekotlinsubquerywindow-functions

解决方案

在 SQLite 3.25 中添加了窗口函数，而您正在运行版本 3.18。

我正在尝试做的需要获得每个测试最后 4 个条目的平均值，

另一种方法是相关子查询：

SELECT 
    created_at, 
    (
        SELECT AVG(value) 
        FROM test_result t1 
        WHERE t1.test = t.test and t1.created_at <= t.created_at
        ORDER BY t1.created_at DESC LIMIT 4
    ) running_avg_value
FROM test_result t

这为您提供了当前行和同一测试的最后 3 条记录的运行平均值，按created_at.

对于性能，请考虑在(test, created_at, value). 即便如此，这可能无法很好地适应大型数据集。如果您要升级到最新版本（应该真正考虑），您可以将子查询替换为：

AVG(value) OVER(PARTITION BY test ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)

推荐阅读