在PRESTO中以布尔值生成结果的有效方法 [英] Effective way in PRESTO to Result output with Boolean values

问题描述

我正在尝试根据某些条件创建布尔值的输出.

Im trying to create a output of boolen values on back of some conditions.

输出:

EX::我从不同的表中获得了3个彼此不相关的规则/条件.

EX: I have 3 rules/conditions from different tables which are not related to each other.

规则1:

Select USER_NAME, ID from session_user where age > 25

规则2:

Select USER_NAME, ID from current_user where plan = 'gold'

规则3:

Select USER_NAME, ID from customer where group_name='managers'

我的输出应该是:

USER_NAME   |  ID | Rule 1 | Rule 2 | Rule 3

user1          1     true    false    true

user2          2     false   true     true

user3          3     true    true     true

如果 user1 传递了规则1，则输出中的值应为 true ，如果他传递了 rule 2 ，则true 否则 false .

If user1 is passing rule one the value should be true for him in the output, if he passed rule 2 then true else false.

解决方案:

SELECT  dp.USER_NAME,
        dp.ID, 
        CASE 
            WHEN dp.sex='F' THEN 'True' 
            ELSE 'False' 
        END AS Rule_1,
        CASE 
            WHEN dp.sex='M' THEN 'True' 
            ELSE 'False' 
        END AS Rule_2,
        CASE 
            WHEN dp.sex not in ('M','F') THEN 'True' 
            ELSE 'False' 
        END AS Rule_3
FROM    user_details dp where dp.Organisation='007';

问题:

1. 上面的select语句在PRESTO中是有效的方法吗?
2. 如果没有其他选择，那么
3. 我不是PRESTO的新手，所以在select中很难使用join，当我联接多个表时如何形成类似的输出?任何文档链接都将有助于进一步了解.
4. 使用 CASE 时，我无法使用 COUNT ，如果我想查看 COUNT 大于2，如何添加规则.(Count(*)> 0)= True，否则为False.
5. 我们可以在 CASE 中使用 SubQuery 吗?会影响性能吗?

1. Is the above select statement is effective way in PRESTO.?
2. If not what are the other options.
3. Im new to PRESTO so its difficult to use join in select, How do i form similar output when i join multiple tables? any doc links will be helpful to understand further.
4. With CASE I cant able to use COUNT how can i add a rule if i want to see COUNT is greater than 2. (Count(*) > 0) = True else False.
5. Can we use SubQuery in CASE? Will it affect performance?

推荐答案

PrestoDB支持布尔值，因此不需要 CASE 表达式:

PrestoDB supports boolean values, so no CASE expression is necessary:

SELECT dp.USER_NAME, dp.ID, 
       (dp.sex = 'F') AS Rule_1,
       (dp.sex = 'M') AS Rule_2,
       (dp.sex not in ('M','F')) AS Rule_3
FROM user_details dp 
WHERE dp.Organisation = '007';

我看不到您其余的问题与您的查询有什么关系.但是，StackOverflow问题仅限于一个问题.因此，随时问另一个问题...但是PrestoDB(像所有数据库一样)在 CASE 表达式中支持子查询.

I don't see what the rest of your questions have to do with your query. However, StackOverflow questions are limited to a single question. So feel free to ask another question . . . but PrestoDB (like all databases) supports subqueries in CASE expressions.

这篇关于在PRESTO中以布尔值生成结果的有效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！