使用不同计数时，如何显示多个字段？

发布于 2025-01-21 16:25:24 字数 496 浏览 3 评论 0原文

我正在尝试使用相同的电子邮件地址获得总数不同的和姓氏的数字，但我不确定从这里去哪里。 field1和field2在同一表中。

我的输出应具有串联字段，字段1，field2

SELECT COUNT(DISTINCT(CONCAT(first_name,last_name)))
   FROM `datalake.core.profile_snapshot` 
   WHERE classic_country = 'US' and
       email.personal = '[email protected]'
   LIMIT 1000

感谢任何帮助！

原文

I am trying to get a count of total different first and last names with the same email address, and I'm not sure where to go from here. Field1 and Field2 are in the same table.

My output should have the concatenated field, field 1, field2

SELECT COUNT(DISTINCT(CONCAT(first_name,last_name)))
   FROM `datalake.core.profile_snapshot` 
   WHERE classic_country = 'US' and
       email.personal = '[email protected]'
   LIMIT 1000

Appreciate any help!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

痴情 2025-01-28 16:25:24

SELECT 
first_name
,last_name
,email_address
,count(1) as number
FROM datalake.core.profile_snapshot
GROUP BY 
first_name
,last_name
,email_address

如果要将结果设置减少到特定的电子邮件地址，请添加一个条款即可。

我已经使用了email_address而不是email.personal。

SELECT 
first_name
,last_name
,email_address
,count(1) as number
FROM datalake.core.profile_snapshot
GROUP BY 
first_name
,last_name
,email_address

If you want to reduce the result set to a particular email address then just add a where clause to do so.

I've used email_address instead of email.personal.

回复收藏 0 原文

岁月无声 2025-01-28 16:25:24

SQL的限制通常限制返回的行数，而不是用于过滤。过滤您的聚合电子邮件

需要使用`具有`以用1000多个不同的名称


SELECT email
    /*Put random pipe character "|" in between first and last name so don't get names that concatenate to same value
    Such as Jane Doe and Jan Edoe. Not a realistic example but concatenation could result in same "value" without a separator*/
    ,DistinctNames = COUNT(DISTINCT CONCAT(first_name,'|',last_name))
FROM datalake.core.profile_snapshot
WHERE classic_country = 'US'
AND email.personal = '[email protected]' /*Can comment this out if you want to see all email with 1000+ distinct names*/
GROUP BY email
/*HAVING clause = WHERE clause for aggregates*/
HAVING COUNT(DISTINCT CONCAT(first_name,'|',last_name)) > 1000 /*1000 distinct names for each email*/

LIMIT for SQL is generally limiting the number of rows returned, not for filtering. Need to use HAVING to filter on your aggregate

Email with 1000+ Distinct Names


SELECT email
    /*Put random pipe character "|" in between first and last name so don't get names that concatenate to same value
    Such as Jane Doe and Jan Edoe. Not a realistic example but concatenation could result in same "value" without a separator*/
    ,DistinctNames = COUNT(DISTINCT CONCAT(first_name,'|',last_name))
FROM datalake.core.profile_snapshot
WHERE classic_country = 'US'
AND email.personal = '[email protected]' /*Can comment this out if you want to see all email with 1000+ distinct names*/
GROUP BY email
/*HAVING clause = WHERE clause for aggregates*/
HAVING COUNT(DISTINCT CONCAT(first_name,'|',last_name)) > 1000 /*1000 distinct names for each email*/

回复收藏 0 原文

~没有更多了~