没有嵌套选择的两个SQL计数

I have an SQL query I need to change not to be nested selects.

我有一个SQL查询,我需要更改不是嵌套选择。

I'm not sure how to do it....

我不知道怎么做......

SELECT 
    (SELECT COUNT(DISTINCT _user_id) FROM [data] WHERE (_language == 'en')) / 
    COUNT(DISTINCT _user_id)
FROM [data]

Preferably not with 'JOIN' (unless there's no other way).

最好不要使用'JOIN'(除非没有别的办法)。

EDIT: I need the ratio between users who uphold the condition and those who don't

编辑:我需要维护条件的用户和不支持条件的用户之间的比例

2 个解决方案

#1

You can do this with conditional aggregation:

您可以使用条件聚合执行此操作:

SELECT (COUNT(DISTINCT (CASE WHEN _language = 'en' THEN _user_id END)) /
        COUNT(DISTINCT _user_id)
       )
FROM data;

The question is tagged MySQL, and yet you are using square braces -- this will cause an error in MySQL. You don't need the square braces anyway. The standard equality comparison is =, not == (even if bigquery supports both).

问题是标记MySQL,但你使用方括号 - 这将导致MySQL中的错误。无论如何你不需要方括号。标准的相等比较是=,而不是==(即使bigquery支持两者)。

#2

You could use an inline case expression:

您可以使用内联案例表达式:

SELECT COUNT(DISTINCT CASE WHEN _language = 'en' THEN _user_id ELSE NULL END) / 
       COUNT(DISTINCT _user_id)
FROM   [data]

#1