计算一组中最大数量的变量 [英] Calculate variable of max amount in a group

问题描述

我很难进行以下运动.我需要查找id不是amount最多的组中的max_id的频率.应该考虑包含至少两个不同人员的组来完成此操作. 数据来自两个不同的表:max_id来自表1(我将其称为a)以及user和amount； id来自table2(b)以及组. 根据上面的文字，条件应为

I have difficulties in doing the following exercise. I would need to find how frequent is that an id is not the max_id in the group with the most amount. This should be done considering groups that contain at least two different people. Data comes from two different tables: max_id comes from table1 (I will call it a)as well as user and amount; id comes from table2 (b) as well as group. From the text above, the conditions should be

(1) a.id<>b.max_id /* is not */
(2) people in group >=2
(3) a.id<> id of max amount

数据集看起来像

(a)

max_id  user  amount

(b)

group email

在以前的练习中，我必须按如下方式计算不同的人:

From a previous exercise, I had to compute distinct people as follows:

sel a.distinct users
a.max_id
b.id
from table1 as a
inner join table2 as b
on b.id=a.max_id

where
b.max_id is not null
and b.time is null

在上面的练习中，无需提供金额信息.这是两个练习之间的主要区别，但是结构和字段非常相似. 现在，我将需要编辑上面的代码，以查找id不是amount最多的组中的max_id的频率.仅当组具有至少两个不同的人员/用户时，这才有意义. 我想我需要加入表以获得组中最大数量的ID并计算组中的人数，但是我不知道该怎么做. 任何帮助将不胜感激.谢谢.

No information from amount was required in the exercise above. This is the main difference between the two exercises, but the structure and fields are quite similar. Now, I would need to edit the code above in order to find how frequent is that an id is not the max_id in the group with the most amount. This makes sense only if groups have at least two different persons/users. I think I will need to join tables to get the id of max amount in a group and count people in a group, but I do not know how to do it. Any help would be greatly appreciated. Thank you.

数据样本

max_id  user  amount id group email
12       1    -2000  12 house email1
312      1      0    54 work  email1
11       32    -213  11 house email32
41       13    -43   78 work  email13
312      53    -650  34 work  email53
1        67    -532  43 defense email67
64       76   -9650  98 work  email76

根据我的理解，练习需要做什么，并根据上面的代码，我应该找到id<>max_id的值，并且在group中具有大于2 users的值(例如，房屋，工作，国防). 然后，我需要选择的是id <> id of max amount.

For my understanding, what the exercise asks and based on the code above, I should find values for id<>max_id and having more than 2 users in a group (i.e. house, work, defence). Then, what I would need to select is id <> id of max amount.

我希望这一点可以更清楚一些.

I hope this it can be a bit more clear.

推荐答案

假设您的查询为

select t.User, m.Email, m.Model, m.Amount
from my_table m
inner join   (
    select user, max(amount) max_amount
    from my_table 
    group by user
) t on t.user = m.user
    and t.max_amount  = m.amount

您可以使用

select max(id), Amount
from (

    select m.id, t.User, m.Email, m.Model, m.Amount
    from my_table m
    inner join   (
        select user, max(amount) max_amount
        from my_table 
        group by user
    ) t on t.user = m.user
        and t.max_amount  = m.amount
) k  

，您应该获得与最大ID不相等的id的值

and you should obtain the valud of id that are not equal to max id as

select mm.id, t.User, mm.Email, mm.Model, mm.Amount
    from my_table mm
    inner join   (
        select user, max(amount) max_amount
        from my_table 
        group by user
    ) t on t.user = m.user
        and t.max_amount  = m.amount
inner join  (
    select max(k.id) max_id, k.Amount
    from (

        select m.id, t.User, m.Email, m.Model, m.Amount
        from my_table m
        inner join   (
            select user, max(amount) max_amount
            from my_table 
            group by user
        ) t on t.user = m.user
            and t.max_amount  = m.amount
    ) k

    ) kk ON kk.max_id <> mm.id

，根据您的上一个样本，查询应为

and based on your last sample the query should be

select m.*
from my_table
inner join  (
    select my_groups, count(distinct user)
    from my_table 
    group by my_groups
    having count(distinct user) >2
) t on t.my_group = m.my_group 
    and m.max_id <> m.id

PS组是保留字，因此我将my_groups用作列名

PS group is a reserved word so i use my_groups for the column name

这篇关于计算一组中最大数量的变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！