代码之家 › 专栏 › 技术社区 › Mike Marks

尝试从单个表(SQL)创建6个分段的数据“组”

tsql sql-server

Mike Marks · 技术社区 · 7 年前

我要做的是获取包含29268条记录的源数据,并从中创建六个不同的、唯一的(通过电子邮件地址,这是数据中的一个字段)数据集。这是我的基础查询,它捕获了4878条记录(并且这个概念在概念上会执行6次,但是我需要做的是每次都能够通过电子邮件地址获得一组新的4878条记录(在连续查询运行中的电子邮件地址不会在先前的运行中存在))。在我的头顶上,我正在考虑做一些排名的事情,但我不确定如何继续做我需要做的事情。我会把自己归类为SQL的中级。这有点过头了。有什么想法吗?

select top 1124 * from
Master_Subscribers_Score_GTE_5
where [E-mail Address] like '%YAHOO.COM%'

union all

select top 402 * from
Master_Subscribers_Score_GTE_5
where ([E-mail Address] like '%HOTMAIL.COM%' or [E-mail Address] like '%LIVE.COM%')

union all

select top 45 * from
Master_Subscribers_Score_GTE_5
where [E-mail Address] like '%AOL.COM%'

union all

select top 2353 * from
Master_Subscribers_Score_GTE_5
where [E-mail Address] like '%GMAIL.COM%'

union all

select top 164 * from
Master_Subscribers_Score_GTE_5
where ([E-mail Address] like '%ATT.COM%' or [E-mail Address] like '%SBCGLOBAL.NET%')

union all

select top 8 * from
Master_Subscribers_Score_GTE_5
where [E-mail Address] like '%COX.NET%'

union all

select top 3 * from
Master_Subscribers_Score_GTE_5
where [E-mail Address] like '%VERIZON.NET%'

union all

select top 70 * from
Master_Subscribers_Score_GTE_5
where [E-mail Address] like '%RR.COM%'

union all

select top 712 * from
Master_Subscribers_Score_GTE_5
where [E-mail Address] not like '%YAHOO.COM%' and
[E-mail Address] not like '%HOTMAIL.COM%' and
[E-mail Address] not like '%LIVE.COM%' and
[E-mail Address] not like '%AOL.COM%' and
[E-mail Address] not like '%GMAIL.COM%' and
[E-mail Address] not like '%ATT.COM%' and
[E-mail Address] not like '%SBCGLOBAL.NET%' and
[E-mail Address] not like '%COX.NET%' and
[E-mail Address] not like '%VERIZON.NET%' and
[E-mail Address] not like '%RR.COM%'

2 回复 | 直到 7 年前

DatumPoint 7 年前

首先,使用 LIKE this post .

您可以使用 SUBSTRING CHARINDEX

下面将获取电子邮件提供程序

SUBSTRING(Email, CHARINDEX('@', Email, 1)+1, LEN(EmailR) - CHARINDEX('@', Email, 1))

现在,既然得到了需要过滤的部分,就用它来过滤记录,然后使用 ROW_NUMBER() CASE 完成记录。

下面是一个例子:

SELECT *
FROM (
    SELECT *
    ,   CASE
            WHEN  UPPER(EmailDomain) = 'YAHOO.COM' AND RN <= 1124 
            THEN 'Group 1'
            WHEN  UPPER(EmailDomain) = 'HOTMAIL.COM' AND RN <= 402
            THEN 'Group 2'
            WHEN  UPPER(EmailDomain) = 'AOL.COM' AND RN <= 45
            THEN 'Group 3'
            WHEN  UPPER(EmailDomain) = 'GMAIL.COM' AND RN <= 2353
            THEN 'Group 4'
            WHEN  (UPPER(EmailDomain) = 'ATT.COM' OR UPPER(EmailDomain) = 'SBCGLOBAL.NET') AND RN < 164
            THEN 'Group 5'
            WHEN  UPPER(EmailDomain) = 'COX.NET'  AND RN <= 8
            THEN 'Group 6'
            WHEN  UPPER(EmailDomain) = 'VERIZON.NET' AND RN <= 3
            THEN 'Group 7'
            WHEN  UPPER(EmailDomain) = 'RR.COM' AND RN <= 70
            THEN 'Group 8'
            WHEN  UPPER(EmailDomain) NOT IN('YAHOO.COM','HOTMAIL.COM','AOL.COM','GMAIL.COM','ATT.COM','SBCGLOBAL.NET','COX.NET','VERIZON.NET','RR.COM') AND RN <= 712
            THEN 'Group 9'
            ELSE NULL
        END EmailGroup
    FROM (
SELECT *, ROW_NUMBER() OVER(PARTITION BY EmailDomain ORDER BY EmailDomain) RN 
FROM (
SELECT 
    Email  
,   SUBSTRING(Email, CHARINDEX('@', Email, 1)+1, LEN(EmailR) - CHARINDEX('@', Email, 1)) EmailDomain 
FROM 
    Master_Subscribers_Score_GTE_5
) D 
) C
) E
WHERE 
    EmailGroup IS NOT NULL

SELECT TOP x . 然后我给了那些在任何情况下都不适合的记录一个空值,这给了我一个简单的方法,只显示我需要的内容,并用空值填充其余的内容,以从结果中排除。

我用过 UPPER() 因为我不知道你的数据库排序规则-是否区分大小写。所以我用它来克服这个问题。如果数据库不区分大小写,则不需要它。

我希望这能有帮助。

gordy 7 年前

with ranked as (
    select m.*, n = row_number() over (partition by b.bucket order by m.[E-mail Address])
    from Master_Subscribers_Score_GTE_5 m
    outer apply (select bucket from (values
        ('yahoo.com'), ('hotmail.com,live.com'),
        ('aol.com'), ('gmail.com'), ('att.com,sbcglobal.net'),
        ('cox.net'), ('verizon.net'), ('rr.com'))
        _(bucket) where exists (
            select * from string_split(bucket, ',')
            where m.[E-mail Address] like '%' + value + '%')) b)

select * from ranked where n % 6 = 0

…雅虎网站应该给你1124,hotmail.com和live.com应该给你402,然后查询 n % 6 = 1 下一组 n % 6 = 2 等等。