我正在尝试在 Django 中创建以下查询
SELECT content_reference , COUNT(reference)
AS total
FROM usage_statistics
WHERE content_type = 'blog'
AND access_date > NOW() - INTERVAL '90' DAY
GROUP BY content_reference
ORDER BY total DESC
LIMIT 10
到目前为止我想出的是:
result = UsageStatistics.objects.values('content_reference')\
.annotate(total=Count('reference'))\
.order_by('total')
这使得查询
SELECT "usage_statistics"."content_reference", COUNT("usage_statistics"."reference") AS "total"
FROM "usage_statistics"
GROUP BY "usage_statistics"."content_reference"
ORDER BY "total" ASC
LIMIT 21
我不确定如何正确包括:
以下是我的usage_statistics表结构
CREATE TABLE usage_statistics
(
reference bigint
access_date timestamp with time zone,
ip_address inet NOT NULL,
language_iso text NOT NULL,
content_type character varying(12) NOT NULL,
content_reference text NOT NULL,
passport_user_id bigint
)
您可以使用 a.filter()
来限制过去 60 天内更改的内容。可以使用数据库函数NOW()
,或者您可以在 python 中进行数学运算:
.filter(access_date__gt=datetime.datetime.utcnow() - datetime.timedelta(days=60))
订购同样是可能的:
.order_by('-total')
最后,您可以对查询集进行切片以获取前 10 个结果:
[:10]
因此,您的最终查询可能类似于:
result = UsageStatistics.objects.filter(
access_date__gte=datetime.datetime.utcnow() - datetime.timedelta(days=60)
).values('content_reference').annotate(
total=Count('reference')
).order_by('-total')[:10]
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句