django - 如何在 Django-ORM 中强制子查询？
问题描述
我有下表：
> select * from request_tripinterval order by id
+-----+----------------------------+-------+------+-----------+---------+
| id | timestamp | path | time | params_id | trip_id |
+-----+----------------------------+-------+------+-----------+---------+
| 411 | 2019-08-25 21:09:18.193870 | 47.0 | 67.0 | 1 | 1 |
| 412 | 2019-08-25 21:09:18.311034 | 99.0 | 67.0 | 1 | 1 |
| 413 | 2019-08-25 21:09:18.436022 | 66.0 | 67.0 | 1 | 1 |
| 414 | 2019-08-25 21:09:18.544529 | 44.0 | 67.0 | 1 | 1 |
| 415 | 2019-08-25 21:09:18.636328 | 16.0 | 67.0 | 1 | 1 |
| 416 | 2019-08-25 21:09:18.728114 | 18.0 | 67.0 | 1 | 1 |
| 417 | 2019-08-25 21:09:18.802324 | 52.0 | 67.0 | 1 | 1 |
| 418 | 2019-08-25 21:09:18.886299 | 92.0 | 52.0 | 1 | 1 |
| 419 | 2019-08-25 21:09:18.977108 | 13.0 | 52.0 | 1 | 1 |
| 420 | 2019-08-25 21:09:19.059118 | 5.0 | 26.0 | 1 | 1 |
| 421 | 2019-08-25 21:09:19.134312 | 91.0 | 26.0 | 1 | 1 |
| 422 | 2019-08-25 21:09:19.209511 | 75.0 | 87.0 | 1 | 1 |
| 423 | 2019-08-25 21:09:19.275903 | 89.0 | 80.0 | 1 | 1 |
| 424 | 2019-08-25 21:09:19.358898 | 59.0 | 80.0 | 1 | 1 |
| 425 | 2019-08-25 21:09:19.427366 | 100.0 | 80.0 | 1 | 1 |
| 426 | 2019-08-25 21:09:19.543564 | 95.0 | 80.0 | 1 | 1 |
| 427 | 2019-08-25 21:09:19.654867 | 91.0 | 80.0 | 1 | 1 |
| 428 | 2019-08-25 21:09:19.742747 | 78.0 | 80.0 | 1 | 1 |
| 429 | 2019-08-25 21:09:19.826721 | 86.0 | 80.0 | 1 | 1 |
| 430 | 2019-08-25 21:09:19.901921 | 5.0 | 80.0 | 1 | 1 |
| 431 | 2019-08-25 21:09:19.976131 | 14.0 | 80.0 | 1 | 1 |
| 432 | 2019-08-25 21:09:20.051317 | 98.0 | 42.0 | 1 | 1 |
| 433 | 2019-08-25 21:09:20.126492 | 15.0 | 42.0 | 1 | 1 |
| 434 | 2019-08-25 21:09:20.216339 | 80.0 | 42.0 | 1 | 1 |
| 435 | 2019-08-25 21:09:20.301297 | 94.0 | 42.0 | 1 | 1 |
| 436 | 2019-08-25 21:09:20.399910 | 99.0 | 42.0 | 1 | 1 |
| 437 | 2019-08-25 21:09:20.466298 | 91.0 | 42.0 | 1 | 1 |
| 438 | 2019-08-25 21:09:20.541497 | 38.0 | 42.0 | 1 | 1 |
| 439 | 2019-08-25 21:09:20.616683 | 36.0 | 42.0 | 1 | 1 |
| 440 | 2019-08-25 21:09:20.701623 | 37.0 | 11.0 | 1 | 1 |
| 441 | 2019-08-25 21:09:20.782666 | 97.0 | 11.0 | 1 | 1 |
| 442 | 2019-08-25 21:09:20.908651 | 36.0 | 11.0 | 1 | 1 |
| 443 | 2019-08-25 21:09:20.991640 | 40.0 | 11.0 | 1 | 1 |
| 444 | 2019-08-25 21:09:21.059015 | 3.0 | 11.0 | 1 | 1 |
| 445 | 2019-08-25 21:09:21.141037 | 35.0 | 11.0 | 1 | 1 |
| 446 | 2019-08-25 21:09:21.240623 | 7.0 | 11.0 | 1 | 1 |
| 447 | 2019-08-25 21:09:21.323633 | 56.0 | 11.0 | 1 | 1 |
| 448 | 2019-08-25 21:09:21.400760 | 4.0 | 11.0 | 1 | 1 |
| 449 | 2019-08-25 21:09:21.491573 | 87.0 | 51.0 | 1 | 1 |
| 450 | 2019-08-25 21:09:21.574581 | 90.0 | 51.0 | 1 | 1 |
| 451 | 2019-08-25 21:09:21.664402 | 34.0 | 33.0 | 1 | 1 |
| 452 | 2019-08-25 21:09:21.748376 | 9.0 | 33.0 | 1 | 1 |
| 453 | 2019-08-25 21:09:21.831374 | 98.0 | 33.0 | 1 | 1 |
| 454 | 2019-08-25 21:09:21.916329 | 67.0 | 33.0 | 1 | 1 |
| 455 | 2019-08-25 21:09:22.007345 | 43.0 | 33.0 | 1 | 1 |
| 456 | 2019-08-25 21:09:22.082520 | 35.0 | 33.0 | 1 | 1 |
| 457 | 2019-08-25 21:09:22.190918 | 69.0 | 33.0 | 1 | 1 |
| 458 | 2019-08-25 21:09:22.273957 | 79.0 | 79.0 | 1 | 1 |
| 459 | 2019-08-25 21:09:22.389165 | 59.0 | 79.0 | 1 | 1 |
| 460 | 2019-08-25 21:09:22.464353 | 27.0 | 79.0 | 1 | 1 |
| 461 | 2019-08-25 21:09:22.563965 | 98.0 | 6.0 | 1 | 1 |
| 462 | 2019-08-25 21:09:22.640127 | 100.0 | 6.0 | 1 | 1 |
| 463 | 2019-08-25 21:09:22.747525 | 4.0 | 6.0 | 1 | 1 |
| 464 | 2019-08-25 21:09:22.822713 | 98.0 | 6.0 | 1 | 1 |
| 465 | 2019-08-25 21:09:22.922326 | 22.0 | 6.0 | 1 | 1 |
| 466 | 2019-08-25 21:09:23.005306 | 47.0 | 6.0 | 1 | 1 |
| 467 | 2019-08-25 21:09:23.088333 | 46.0 | 6.0 | 1 | 1 |
| 468 | 2019-08-25 21:09:23.155681 | 13.0 | 6.0 | 1 | 1 |
| 469 | 2019-08-25 21:09:23.231856 | 75.0 | 6.0 | 1 | 1 |
| 470 | 2019-08-25 21:09:23.298255 | 87.0 | 6.0 | 1 | 1 |
| 471 | 2019-08-25 21:09:23.372465 | 4.0 | 16.0 | 1 | 1 |
| 472 | 2019-08-25 21:09:23.448619 | 67.0 | 16.0 | 1 | 1 |
| 473 | 2019-08-25 21:09:23.521861 | 22.0 | 16.0 | 1 | 1 |
| 474 | 2019-08-25 21:09:23.631304 | 51.0 | 19.0 | 1 | 1 |
| 475 | 2019-08-25 21:09:23.706488 | 16.0 | 19.0 | 1 | 1 |
| 476 | 2019-08-25 21:09:23.798287 | 87.0 | 32.0 | 1 | 1 |
| 477 | 2019-08-25 21:09:23.890063 | 13.0 | 32.0 | 1 | 1 |
| 478 | 2019-08-25 21:09:23.980984 | 78.0 | 32.0 | 1 | 1 |
| 479 | 2019-08-25 21:09:24.063983 | 84.0 | 32.0 | 1 | 1 |
| 480 | 2019-08-25 21:09:24.138198 | 12.0 | 32.0 | 1 | 1 |
| 481 | 2019-08-25 21:09:24.221197 | 27.0 | 79.0 | 1 | 1 |
| 482 | 2019-08-25 21:09:24.296366 | 26.0 | 79.0 | 1 | 1 |
| 483 | 2019-08-25 21:09:24.388154 | 5.0 | 79.0 | 1 | 1 |
| 484 | 2019-08-25 21:09:24.463434 | 45.0 | 79.0 | 1 | 1 |
| 485 | 2019-08-25 21:09:24.554256 | 31.0 | 79.0 | 1 | 1 |
| 486 | 2019-08-25 21:09:24.638232 | 40.0 | 79.0 | 1 | 1 |
| 487 | 2019-08-25 21:09:24.746605 | 63.0 | 79.0 | 1 | 1 |
| 488 | 2019-08-25 21:09:24.829607 | 80.0 | 81.0 | 1 | 1 |
| 489 | 2019-08-25 21:09:24.937993 | 69.0 | 81.0 | 1 | 1 |
| 490 | 2019-08-25 21:09:25.021977 | 67.0 | 81.0 | 1 | 1 |
| 491 | 2019-08-25 21:09:25.129387 | 82.0 | 81.0 | 1 | 1 |
| 492 | 2019-08-25 21:09:25.245572 | 74.0 | 81.0 | 1 | 1 |
| 493 | 2019-08-25 21:09:25.379345 | 25.0 | 81.0 | 1 | 1 |
| 494 | 2019-08-25 21:09:25.544364 | 23.0 | 81.0 | 1 | 1 |
| 495 | 2019-08-25 21:09:25.628341 | 24.0 | 81.0 | 1 | 1 |
| 496 | 2019-08-25 21:09:25.702550 | 68.0 | 81.0 | 1 | 1 |
| 497 | 2019-08-25 21:09:25.777737 | 68.0 | 95.0 | 1 | 1 |
| 498 | 2019-08-25 21:09:25.862701 | 41.0 | 95.0 | 1 | 1 |
| 499 | 2019-08-25 21:09:25.945713 | 73.0 | 95.0 | 1 | 1 |
| 500 | 2019-08-25 21:09:26.035520 | 44.0 | 95.0 | 1 | 1 |
| 501 | 2019-08-25 21:09:26.110712 | 75.0 | 95.0 | 1 | 1 |
| 502 | 2019-08-25 21:09:26.185917 | 73.0 | 95.0 | 1 | 1 |
| 503 | 2019-08-25 21:09:26.270858 | 5.0 | 95.0 | 1 | 1 |
| 504 | 2019-08-25 21:09:26.353856 | 77.0 | 95.0 | 1 | 1 |
| 505 | 2019-08-25 21:09:26.443675 | 47.0 | 95.0 | 1 | 1 |
| 506 | 2019-08-25 21:09:26.526674 | 75.0 | 29.0 | 1 | 1 |
| 507 | 2019-08-25 21:09:26.618461 | 66.0 | 29.0 | 1 | 1 |
| 508 | 2019-08-25 21:09:26.693664 | 60.0 | 44.0 | 1 | 1 |
| 509 | 2019-08-25 21:09:26.785455 | 48.0 | 44.0 | 1 | 1 |
| 510 | 2019-08-25 21:09:26.852823 | 51.0 | 44.0 | 1 | 1 |
+-----+----------------------------+-------+------+-----------+---------+
当我像这样从 Django-project 查询它时：
id_row = Window(RowNumber(), order_by=F('id').asc())
time_row = Window(RowNumber(), partition_by=F('time'), order_by=F('id').asc())
queryset = TripInterval.objects.annotate(
time_grp=id_row - time_row
).values(
'time', 'time_grp'
).annotate(
start_id=Min('id'), end_id=Max('id')
).order_by(
'start_id'
)
它会产生以下 SQL：
SELECT
"request_tripinterval"."time",
(
ROW_NUMBER() OVER (ORDER BY "request_tripinterval"."id" ASC)
-
ROW_NUMBER() OVER (PARTITION BY "request_tripinterval"."time" ORDER BY "request_tripinterval"."id" ASC)
) AS "time_grp",
MIN("request_tripinterval"."id") AS "start_id",
MAX("request_tripinterval"."id") AS "end_id"
FROM
"request_tripinterval"
GROUP BY
"request_tripinterval"."time",
(
ROW_NUMBER() OVER (ORDER BY "request_tripinterval"."id" ASC)
-
ROW_NUMBER() OVER (PARTITION BY "request_tripinterval"."time" ORDER BY "request_tripinterval"."id" ASC)
)
ORDER BY "start_id" ASC
这给出了一个错误：
滥用窗口函数 ROW_NUMBER()
但我真正需要的是：
SELECT "subquery"."time" AS "time",
MIN("subquery"."id") AS "start_id",
MAX("subquery"."id") AS "end_id"
FROM (
SELECT
"request_tripinterval"."id",
"request_tripinterval"."time",
(
ROW_NUMBER() OVER (ORDER BY "request_tripinterval"."id" ASC)
-
ROW_NUMBER() OVER (PARTITION BY "request_tripinterval"."time" ORDER BY "request_tripinterval"."id" ASC)
) AS "time_grp"
FROM
"request_tripinterval"
) as subquery
GROUP BY "time", "time_grp"
ORDER BY "start_id" ASC
效果很好并给出了所需的结果：
+------+----------+--------+
| time | start_id | end_id |
+------+----------+--------+
| 67.0 | 411 | 417 |
| 52.0 | 418 | 419 |
| 26.0 | 420 | 421 |
| 87.0 | 422 | 422 |
| 80.0 | 423 | 431 |
| 42.0 | 432 | 439 |
| 11.0 | 440 | 448 |
| 51.0 | 449 | 450 |
| 33.0 | 451 | 457 |
| 79.0 | 458 | 460 |
| 6.0 | 461 | 470 |
| 16.0 | 471 | 473 |
| 19.0 | 474 | 475 |
| 32.0 | 476 | 480 |
| 79.0 | 481 | 487 |
| 81.0 | 488 | 496 |
| 95.0 | 497 | 505 |
| 29.0 | 506 | 507 |
| 44.0 | 508 | 510 |
+------+----------+--------+
如何在没有 RawSQL 的情况下使用 Django-ORM 构建查询以获得所需的结果？一般来说，我如何在 Django-ORM 中将子查询指定为“FROM”SQL 子句？
解决方案
推荐阅读
- c# - 我可以在 Microsoft 的 Bot Framework 上使用 .NET core 3.0 SDK 吗？
- go - 如何将消息从一个渠道分享到另一个渠道？
- reactjs - 无法在“React.js”中的函数中获取当前状态值
- sql - 我需要一个 Row_number over partition 在 SQL Server 中实现这一点
- html - Angular 反应式表单下拉黑客在透明度
- elasticsearch - 在索引期间为缺失字段设置默认值
- javascript - 如何在 div 的网格容器中移动 div？
- c# - 将 Libvlc videoview 包含到布局中
- android-studio - Android studio 在执行 lint 时找不到 kotlin-compiler.jar 错误
- ios - 如何在我的应用程序中禁用暗模式