如何在一个SQL查询中对不同的值进行分组并计算字段

本文介绍了如何在一个SQL查询中对不同的值进行分组并计算字段的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

这是我第二次使用Stack Overflow，因此我对如何更好地格式化问题持任何建设性的批评态度.

This is only my second time using Stack Overflow so I'm open to any constructive criticism on how to better format my questions.

我有一个订单列表，我想创建一个有用的客户信息表.

I have a list of orders and I'm wanting to create a table of useful customer information.

我创建了一个新表来标识唯一客户(仅使用选择的不同的客户ID)，但是我不确定是否有适当的功能来准确地将其分组并根据其随附字段产生布尔值.

I've created a new table that identifies unique customers (using just a select distinct of customer IDs) but I'm not sure of the proper functions to accurately group them and produce a boolean value based on their accompanying fields.

我需要在新字段中显示一个布尔值，以了解是否有任何客户的订单是针对特定产品SKU的.

I need the new fields to display a boolean for whether or not any of the customer's orders have been for a particular product SKU.

想象这是源表

NAME | PRODUCT ------------ Andy | 1 Bill | 2 Cole | 2 Andy | 2 Bill | 1 Cole | 2 Dave | 3

我希望输出的每个名称仅具有唯一的值，并带有一个布尔值，显示该给定名称的任何记录是否已收到该产品.

I'm wanting the output to only have unique values for each name accompanied by a boolean displaying whether or not any record of that given name has received that product.

NAME | HAS1 | HAS2 | HAS3 -------------------------- Andy | true | true | false Bill | true | true | false Cole | false | true | false Dave | false | false | true

推荐答案

下面是BigQuery标准SQL

Below is for BigQuery Standard SQL

如果您事先知道产品名称(例如您的示例中的"1"，"2"，"3")，则数量很少-您可以在下面的简单版本中使用

If you know in advance product names (like '1', '2', '3' in your example) and there are just few - you can use below simple version

#standardSQL SELECT name, MAX(product = '1') AS has1, MAX(product = '2') AS has2, MAX(product = '3') AS has3 FROM `project.dataset.table` GROUP BY name

如果要应用于您的问题的样本数据(我假设您的产品属于字符串数据类型)

If to apply to sample data from your question (I assume your product are of string data type here)

WITH `project.dataset.table` AS ( SELECT 'Andy' name, '1' product UNION ALL SELECT 'Bill', '2' UNION ALL SELECT 'Cole', '2' UNION ALL SELECT 'Andy', '2' UNION ALL SELECT 'Bill', '1' UNION ALL SELECT 'Cole', '2' UNION ALL SELECT 'Dave', '3' )

结果是

Row name has1 has2 has3 1 Andy true true false 2 Bill true true false 3 Cole false true false 4 Dave false false true

如果事先不知道产品名称和/或产品数量不止几个，可以使用以下版本

In case if product names are not known in advance and/or number of products more than just few - below version can be handy

EXECUTE IMMEDIATE ''' SELECT name,''' || ( SELECT STRING_AGG(DISTINCT "MAX(product = '" || product || "') AS has" || product) FROM `project.dataset.table` ) || ''' FROM `project.dataset.table` GROUP BY name '''

具有完全相同的输出

正如您在这里看到的那样-整个查询是动态组装的，因此您不必担心产品的数量及其名称

As you can see here - whole query is assembled dynamically so you don't need to worry about number of products and their names

以下版本与上面的版本相同，但更易于管理/阅读

Below version is identical to above, but easier to manage/read

EXECUTE IMMEDIATE FORMAT(''' SELECT name, %s FROM `project.dataset.table` GROUP BY name ''', ( SELECT STRING_AGG(DISTINCT "MAX(product = '" || product || "') AS has" || product) FROM `project.dataset.table` ))