查询基于与电子邮件关联的最新名称检索唯一的电子邮件地址

Question

表定义：

CREATE TABLE "USERS"
   (    "USER_ID" NUMBER,
    "EMAIL" VARCHAR2(100),
    "GENDER" CHAR(1),
    "AGE" NUMBER,
    "NAME" VARCHAR2(50),
     CONSTRAINT "USERS_PK" PRIMARY KEY ("USER_ID");
 CREATE TABLE "SUBSCRIPTIONS"
   (    "SUBSCRIPTION_ID" NUMBER,
    "USER_ID" NUMBER NOT NULL ENABLE,
    "SUBSCRIPTION_TYPE" VARCHAR2(50),
    "ACTIVE_INDICATOR" VARCHAR2(3),
     CONSTRAINT "SUBS_PK" PRIMARY KEY ("SUBSCRIPTION_ID");
  CREATE TABLE "TRANSACTIONS"
   (    "SUBSCRIPTION_ID" NUMBER,
    "ACTION" VARCHAR2(50),
    "TIMESTAMP" DATE,
     CONSTRAINT "TR_PK" PRIMARY KEY ("SUBSCRIPTION_ID", "ACTION", "TIMESTAMP");

示例数据：

用户表：

insert into users values(1,'[e-mail]','m',30,'rob');
insert into users values(2,'[e-mail]','m',31,'robert');
insert into users values(3,'[e-mail]','f',18,'lucie');
insert into users values(4,'[e-mail]','f',22,'lulu');
insert into users values(5,'[e-mail]','m',10,'kim');
insert into users values(6,'[e-mail]','f',18,'kim');
insert into users values(7,'[e-mail]','f',8,'kim');
insert into users values(8,'[e-mail]','f',18,'jj');
insert into users values(9,'[e-mail]','m',22,'jay');
insert into users values(10,'[e-mail]','f',88,'bill');
insert into users values(11,'[e-mail]','f',88,'will');
insert into users values(12,'[e-mail]','f',60,'will');
insert into users values(13,'[e-mail]','m',70,'george');

订阅表：

insert into subscriptions values(1,2,'Magazine','yes');
insert into subscriptions values(2,3,'music cd','no');
insert into subscriptions values(3,3,'magazine','yes');
insert into subscriptions values(4,3,'video','yes');
insert into subscriptions values(5,8,'magazine','yes');
insert into subscriptions values(6,9,'video','yes');
insert into subscriptions values(7,10,'magazine','no');
insert into subscriptions values(8,13,'magazine','yes');

交易表：

insert into transaction values(1,'renewal','10-SEP-02');
insert into transaction values(2,'cancellation','01-FEB-02');
insert into transaction values(2,'renewal','01-JAN-02');
insert into transaction values(3,'renewal','20-AUG-02');
insert into transaction values(4,'renewal','01-AUG-02');
insert into transaction values(4,'renewal','01-SEP-02');
insert into transaction values(5,'renewal','01-AUG-02');
insert into transaction values(6,'renewal','01-SEP-01');
insert into transaction values(7,'cancellation','10-SEP-02');
insert into transaction values(7,'renewal','01-SEP-02');

您将如何生成具有该电子邮件地址的用户最新名称，性别和年龄的唯一电子邮件地址列表？

选择标准将列表限制为 -

从未订阅任何东西的用户；或者;

用户具有无效订阅；或者;

具有主动订阅的用户在任何一年的9月1日至9月30日之间进行了续订

输出应为以下。

Email       Gender  Age Name
[e-mail]         m   31  robert 
[e-mail]         f   22  lulu 
[e-mail]         f   08  kim 
[e-mail]         m   22  Jay 
[e-mail]         f   60  Will

我尝试了以下查询：

select email, gender, age, name
from (
    select *
    from (
        select email, gender, age, name, row_number() over (
                partition by email order by age desc
                ) rn
        from (
            select *
            from users
            where user_id not in (
                    select user_id
                    from subscriptions
                    where active_indicator = 'yes'
                    )
                or user_id in (
                    select user_id
                    from subscriptions s
                    join transactions t on s.subscription_id = t.subscription_id
                    where s.active_indicator = 'yes'
                        and t.action='renewal'
                        and extract(month from t.timestamp) = 9
                        and extract(day from t.timestamp) between 1 and 30
                    )
            )
        )
    )
where rn = 1;
EMAIL            G          AGE     NAME
---------------  -         -----    -----------------
[e-mail]          m         31       robert
[e-mail]          f         22       lulu
[e-mail]          f         18       kim
[e-mail]          m         22       jay
[e-mail]          f         88       bill

我的输出不正确！

如何生成输出？

Answer 1

我认为您要做的就是按user_id订购，而不是窗口函数中的年龄，以获取具有最新user_id的记录，而不是年龄最高。

select email, gender, age, name
from (
    select *
    from (
        select email, gender, age, name, row_number() over (
                partition by email order by user_id desc
                ) rn
        from (
            select *
            from users
            where user_id not in (
                    select user_id
                    from subscriptions
                    where active_indicator = 'yes'
                    )
                or user_id in (
                    select user_id
                    from subscriptions s
                    join transactions t on s.subscription_id = t.subscription_id
                    where s.active_indicator = 'yes'
                        and extract(month from t.timestamp) = 9
                        and extract(day from t.timestamp) between 1 and 30
                    )
            )
        )
    )
where rn = 1;

将产生：

[e-mail] m   31  robert
[e-mail] f   22  lulu
[e-mail] f   8   kim
[e-mail] m   22  jay
[e-mail] f   60  will