以上就是给各位分享DjangoCount和Sum批注相互干扰，同时本文还将给你拓展327.CountofRangeSum、C++如何一起使用std::adjacent和std::count_if来计算
以上就是给各位分享Django Count和Sum批注相互干扰，同时本文还将给你拓展327. Count of Range Sum、C ++如何一起使用std :: adjacent和std :: count_if来计算向量中条件出现的次数、css – django模板forloop.counter问题、django count(*) 慢查询优化等相关知识，如果能碰巧解决你现在面临的问题，别忘了关注本站，现在开始吧！
本文目录一览：- Django Count和Sum批注相互干扰
- 327. Count of Range Sum
- C ++如何一起使用std :: adjacent和std :: count_if来计算向量中条件出现的次数
- css – django模板forloop.counter问题
- django count(*) 慢查询优化
Django Count和Sum批注相互干扰
在构建QuerySet带有多个批注的复合体时，我遇到了一个问题，该问题可以通过以下简单设置重现。
这些是模型：
class Player(models.Model): name = models.CharField(max_length=200)class Unit(models.Model): player = models.ForeignKey(Player, on_delete=models.CASCADE, related_name=''unit_set'') rarity = models.IntegerField()class Weapon(models.Model): unit = models.ForeignKey(Unit, on_delete=models.CASCADE, related_name=''weapon_set'')使用我的测试数据库，可以获得以下（正确）结果：
Player.objects.annotate(weapon_count=Count(''unit_set__weapon_set''))[{''id'': 1, ''name'': ''James'', ''weapon_count'': 23}, {''id'': 2, ''name'': ''Max'', ''weapon_count'': 41}, {''id'': 3, ''name'': ''Bob'', ''weapon_count'': 26}]Player.objects.annotate(rarity_sum=Sum(''unit_set__rarity''))[{''id'': 1, ''name'': ''James'', ''rarity_sum'': 42}, {''id'': 2, ''name'': ''Max'', ''rarity_sum'': 89}, {''id'': 3, ''name'': ''Bob'', ''rarity_sum'': 67}]如果现在将两个批注合并到相同的QuerySet，我将获得不同的（不准确的）结果：
Player.objects.annotate( weapon_count=Count(''unit_set__weapon_set'', distinct=True), rarity_sum=Sum(''unit_set__rarity''))[{''id'': 1, ''name'': ''James'', ''weapon_count'': 23, ''rarity_sum'': 99}, {''id'': 2, ''name'': ''Max'', ''weapon_count'': 41, ''rarity_sum'': 183}, {''id'': 3, ''name'': ''Bob'', ''weapon_count'': 26, ''rarity_sum'': 113}]请注意，rarity_sum现在与以前相比具有不同的值。删除distinct=True不会影响结果。我还尝试使用此答案中的DistinctSum函数，在这种情况下，所有函数都设置为（也不准确）。rarity_sum``18
为什么是这样？如何将两个注释同时合并QuerySet？
编辑 ：这是由组合QuerySet生成的sqlite查询：
SELECT "sandbox_player"."id", "sandbox_player"."name", COUNT(DISTINCT "sandbox_weapon"."id") AS "weapon_count", SUM("sandbox_unit"."rarity") AS "rarity_sum"FROM "sandbox_player" LEFT OUTER JOIN "sandbox_unit" ON ("sandbox_player"."id" = "sandbox_unit"."player_id") LEFT OUTER JOIN "sandbox_weapon" ON ("sandbox_unit"."id" = "sandbox_weapon"."unit_id")GROUP BY "sandbox_player"."id", "sandbox_player"."name"用于以上结果的数据可在此处获得。
答案1
小编典典这不是Django ORM的问题，这只是关系数据库的工作方式。当您构建简单的查询集时，例如
Player.objects.annotate(weapon_count=Count(''unit_set__weapon_set''))要么
Player.objects.annotate(rarity_sum=Sum(''unit_set__rarity''))ORM不正是你希望它做什么-加入Player与Weapon
SELECT "sandbox_player"."id", "sandbox_player"."name", COUNT("sandbox_weapon"."id") AS "weapon_count"FROM "sandbox_player"LEFT OUTER JOIN "sandbox_unit" ON ("sandbox_player"."id" = "sandbox_unit"."player_id")LEFT OUTER JOIN "sandbox_weapon" ON ("sandbox_unit"."id" = "sandbox_weapon"."unit_id")GROUP BY "sandbox_player"."id", "sandbox_player"."name"或Player搭配Unit
SELECT "sandbox_player"."id", "sandbox_player"."name", SUM("sandbox_unit"."rarity") AS "rarity_sum"FROM "sandbox_player"LEFT OUTER JOIN "sandbox_unit" ON ("sandbox_player"."id" = "sandbox_unit"."player_id")GROUP BY "sandbox_player"."id", "sandbox_player"."name"并对其执行COUNT或SUM聚合。
请注意，尽管第一个查询在三个表之间具有两个联接，但是中间表Unit既不在引用的列中SELECT也不在GROUPBY子句中。那唯一的作用Unit在这里踢球是加入Player同Weapon。
现在，如果您查看第三个查询集，事情将变得更加复杂。再次，如在第一个查询中一样，联接位于三个表之间，但现在由于存在以下汇总而Unit被引用：SELECT``SUM``Unit.rarity
SELECT "sandbox_player"."id", "sandbox_player"."name", COUNT(DISTINCT "sandbox_weapon"."id") AS "weapon_count", SUM("sandbox_unit"."rarity") AS "rarity_sum"FROM "sandbox_player" LEFT OUTER JOIN "sandbox_unit" ON ("sandbox_player"."id" = "sandbox_unit"."player_id") LEFT OUTER JOIN "sandbox_weapon" ON ("sandbox_unit"."id" = "sandbox_weapon"."unit_id")GROUP BY "sandbox_player"."id", "sandbox_player"."name"这是第二和第三查询之间的关键区别。在第二个查询，要加入Player到Unit，所以单Unit将再次为每个玩家，它引用被列出。
但在第三个查询要加入Player到Unit再Unit至Weapon，所以不能只有一个Unit会被列出一次为每个玩家，它的参考，
同时也为每个武器引用Unit。
让我们看一个简单的例子：
insert into sandbox_player values (1, "player_1");insert into sandbox_unit values(1, 10, 1);insert into sandbox_weapon values (1, 1), (2, 1);一个玩家，一个单位和两个引用相同单位的武器。
确认问题存在：
>>> from sandbox.models import Player>>> from django.db.models import Count, Sum>>> Player.objects.annotate(weapon_count=Count(''unit_set__weapon_set'')).values()<QuerySet [{''id'': 1, ''name'': ''player_1'', ''weapon_count'': 2}]>>>> Player.objects.annotate(rarity_sum=Sum(''unit_set__rarity'')).values()<QuerySet [{''id'': 1, ''name'': ''player_1'', ''rarity_sum'': 10}]>>>> Player.objects.annotate(... weapon_count=Count(''unit_set__weapon_set'', distinct=True),... rarity_sum=Sum(''unit_set__rarity'')).values()<QuerySet [{''id'': 1, ''name'': ''player_1'', ''weapon_count'': 2, ''rarity_sum'': 20}]>从该示例可以很容易地看出问题是在组合查询中该单元将被列出两次，而引用该单元的每种武器都将被列出一次：
sqlite> SELECT "sandbox_player"."id", ...> "sandbox_player"."name", ...> "sandbox_weapon"."id", ...> "sandbox_unit"."rarity" ...> FROM "sandbox_player" ...> LEFT OUTER JOIN "sandbox_unit" ON ("sandbox_player"."id" = "sandbox_unit"."player_id") ...> LEFT OUTER JOIN "sandbox_weapon" ON ("sandbox_unit"."id" = "sandbox_weapon"."unit_id");id name id rarity ---------- ---------- ---------- ----------1 player_1 1 10 1 player_1 2 10你该怎么办？
正如@ivissani所提到的，最简单的解决方案之一是为每个聚合编写子查询：
>>> from django.db.models import Count, IntegerField, OuterRef, Subquery, Sum>>> weapon_count = Player.objects.annotate(weapon_count=Count(''unit_set__weapon_set'')).filter(pk=OuterRef(''pk''))>>> rarity_sum = Player.objects.annotate(rarity_sum=Sum(''unit_set__rarity'')).filter(pk=OuterRef(''pk''))>>> qs = Player.objects.annotate(... weapon_count=Subquery(weapon_count.values(''weapon_count''), output_field=IntegerField()),... rarity_sum=Subquery(rarity_sum.values(''rarity_sum''), output_field=IntegerField())... )>>> qs.values()<QuerySet [{''id'': 1, ''name'': ''player_1'', ''weapon_count'': 2, ''rarity_sum'': 10}]>产生以下SQL
SELECT "sandbox_player"."id", "sandbox_player"."name", ( SELECT COUNT(U2."id") AS "weapon_count" FROM "sandbox_player" U0 LEFT OUTER JOIN "sandbox_unit" U1 ON (U0."id" = U1."player_id") LEFT OUTER JOIN "sandbox_weapon" U2 ON (U1."id" = U2."unit_id") WHERE U0."id" = ("sandbox_player"."id") GROUP BY U0."id", U0."name") AS "weapon_count", ( SELECT SUM(U1."rarity") AS "rarity_sum" FROM "sandbox_player" U0 LEFT OUTER JOIN "sandbox_unit" U1 ON (U0."id" = U1."player_id") WHERE U0."id" = ("sandbox_player"."id")GROUP BY U0."id", U0."name") AS "rarity_sum"FROM "sandbox_player"
327. Count of Range Sum
327. Count of Range Sum
题目链接：https://leetcode.com/problems...
这题实际就是给定范围内的range sum，divide and conquer的方法。一路计算prefixSum[0:i]，并把结果放进tree里面，然后计算到prefixSum[0:j+1]的时候，找tree里面有没有满足条件的prefixSum[0:i]，这里的条件是lower <= sum[0:j+1] - sum[0:i] <= upper，那么可知sum[0:j+1] - upper <= sum[0:i] <= sum[0:j+1] - lower，那么这个就一个recursion就好了。注意一开始把0加进去，考虑结果是sum[0:j]的情况，还有要用long型，以免sum会overflow
public class Solution {
public int countRangeSum(int[] nums, int lower, int upper) {
int n = nums.length;
if(n == 0) return 0;
// binary search tree
Node root = new Node(0);
int res = 0;
long prefixSum = 0;
for(int i = 0; i < n; i++) {
prefixSum += nums[i];
res += findNumInBound(root, lower, upper, prefixSum);
insert(root, prefixSum);
}
return res;
}
private int findNumInBound(Node node, long low, long up, long sum) {
// base case
if(node == null) return 0;
// range: sum - up <= node.val <= sum - low
if(node.val < sum - up) return findNumInBound(node.right, low, up, sum);
else if(node.val > sum - low) return findNumInBound(node.left, low, up, sum);
else return 1 + findNumInBound(node.left, low, up, sum) + findNumInBound(node.right, low, up, sum);
}
private void insert(Node node, long value) {
while(node != null) {
if(node.val > value) {
if(node.left == null) {
node.left = new Node(value);
break;
}
node = node.left;
}
else {
if(node.right == null) {
node.right = new Node(value);
break;
}
node = node.right;
}
}
}
class Node {
long val;
Node left;
Node right;
Node(long val) { this.val = val; }
}
}还是可以binary index tree来做，要统计sum[0:j+1] - upper <= sum[0:i] <= sum[0:j+1] - lower范围内的个数，就是用sum。参考博客：
http://bookshadow.com/weblog/...
public class Solution {
public int countRangeSum(int[] nums, int lower, int upper) {
int n = nums.length;
if(n == 0) return 0;
// prefix array
long[] prefixSum = new long[n];
for(int i = 0; i < n; i++) {
prefixSum[i] = (i > 0 ? prefixSum[i-1] : 0) + nums[i];
}
long[] sorted = Arrays.copyOf(prefixSum, prefixSum.length);
Arrays.sort(sorted);
// binary index tree
map = new HashMap();
int idx = 1;
for(long sum : sorted) {
if(!map.containsKey(sum)) map.put(sum, idx++);
}
// build tree
BIT t = new BIT(idx);
int res = 0;
for(int i = 0; i < n; i++) {
int l = binarySearch(sorted, prefixSum[i] - upper - 1);
int r = binarySearch(sorted, prefixSum[i] - lower);
res += t.sum(r) - t.sum(l);
if(prefixSum[i] >= lower && prefixSum[i] <= upper) res += 1;
t.add(map.get(prefixSum[i]), 1);
}
return res;
}
Map<Long, Integer> map;
// find the last element <= val
private int binarySearch(long[] arr, long val) {
int l = 0, r = arr.length - 1;
while(l < r) {
int mid = l + (r - l) / 2 + 1;
if(arr[mid] <= val) l = mid;
else r = mid - 1;
}
if(arr[l] > val) return 0;
return map.get(arr[l]);
}
class BIT {
int n;
int[] tree;
BIT(int n) { this.n = n; tree = new int[n]; }
protected int sum(int i) {
int res = 0;
while(i > 0) {
res += tree[i];
i -= i & -i;
}
return res;
}
protected void add(int i, int val) {
while(i < n) {
tree[i] += val;
i += i & -i;
}
}
}
}
C ++如何一起使用std :: adjacent和std :: count_if来计算向量中条件出现的次数
好的，据我了解，问题在于未定义较少的运算符，因此这里是示例解决方案。
int mv::class::countOccurances(const std::vector<OpListIterator>& sortedops)
{
std::count_if(sortedops.begin(),sortedops.end(),[](const opListIterator& a,const opListIterator& b){
std::pair<unsigned,unsigned> A (a->get<unsigned>("Level1"),a->get<unsigned>("Level2"));
std::pair<unsigned,unsigned> B (b->get<unsigned>("Level1"),b->get<unsigned>("Level2"));
return ( A.first == B.first ? A.second < B.second : A.first < B.first);
});
}
首先简化一下，让我们介绍一下投影功能：
std::pair<unsigned,unsigned> projection(opListIterator it)
{
return { it->get<unsigned>("Level1"),it->get<unsigned>("Level2")};
}
您可能会滥用带有该奇怪谓词的std::adjacent_find：
int count = 0;
std::adjacent_find(sortedops.begin(),[&](opListIterator current,opListIterator next) {
count += projection(current) > projection(next);
return false; // never considered as equal
});
return count;
正确的使用方式是：
int count = 0;
auto it = sortedops.begin();
while (it != sortedops.end()) {
it = std::adjacent_find(it,opListIterator next) {
return projection(current) > projection(next);
});
if (it != sortedops.end()) { ++count; ++it; }
}
return count;
对于std::count_if，范围（ranges-v3或C ++ 20）可能会有所帮助：
auto range = ranges::view::zip(sortedops,sortedops | ranges::view::drop(1)); // current/next pairs
return std::count_if(begin(range),end(range),[](const auto& p){
return projection(p.first) > projection(p.second);
});
css – django模板forloop.counter问题
我有很多领域,我正在尝试将不同的CSS应用到邻居表单字段
<li> <field> </li> <li> <field> </li>
如果有一种方式
{% for field in form %}
**{% if forloop.counter%2 == 0 %}**
<li>
{% else%}
<li>
{%endif}
{{field}}
</li>
{% endfor %}
forloop.counter？
非常感谢！
解决方法
cycle tag是为这种类型的问题设计的：
{% for field in form %}
<li>{{ field }}</li>
{% endfor %}
django count(*) 慢查询优化
分页显示是web开发常见需求，随着表数据增加，200万以上时，翻页越到后面越慢，这个时候慢查询成为一个痛点，关于count(*)慢的原因，简单说会进行全表扫描，再排序，导致查询变慢。这里介绍postgresql一种解决方案。对于大表，我们有时候并不需要返回精确的数值，可以采用模糊的总数代替。
原始语句
SELECT COUNT(*) AS "__count" FROM "my_table"优化语句
SELECT reltuples::numeric FROM pg_class WHERE relname = table_name
介绍Django admin 分页优化
from django.contrib.auth.admin import UserAdmin
from django.core.paginator import Paginator, cached_property
from django.db import connections
class UserAdmin(UserAdmin):
paginator = TablePaginator
class TablePaginator(Paginator):
@cached_property
def count(self):
return (
self._get_pgsql_estimate_count()
if not self.object_list.query.where
else super(LargeTablePaginator, self).count
)
def _get_pgsql_estimate_count(self):
with connections["default"].cursor() as cursor:
cursor.execute(
"SELECT reltuples::numeric FROM pg_class WHERE relname = %s",
[self.object_list.query.model._meta.db_table],
)
total_count = int(cursor.fetchone()[0])
return total_count
关于Django Count和Sum批注相互干扰的介绍已经告一段落，感谢您的耐心阅读，如果想了解更多关于327. Count of Range Sum、C ++如何一起使用std :: adjacent和std :: count_if来计算向量中条件出现的次数、css – django模板forloop.counter问题、django count(*) 慢查询优化的相关信息，请在本站寻找。
本文标签：
![[转帖]Ubuntu 安装 Wine方法（ubuntu如何安装wine）](https://www.gvkun.com/zb_users/cache/thumbs/4c83df0e2303284d68480d1b1378581d-180-120-1.jpg)
