开源云原生数据仓库 ByConity 测试

ssh -p 23 <提供的用户名>@<ECS服务器IP地址>举个例子：ssh -p 23 root@14.103.145.182

tmux new -s $user_id
举个例子：tmux new -s user0001

select    cd_gender,  cd_marital_status,  cd_education_status,  count(*) cnt1,  cd_purchase_estimate,  count(*) cnt2,  cd_credit_rating,  count(*) cnt3,  cd_dep_count,  count(*) cnt4,  cd_dep_employed_count,  count(*) cnt5,  cd_dep_college_count,  count(*) cnt6 from  customer c,customer_address ca,customer_demographics where  c.c_current_addr_sk = ca.ca_address_sk and  ca_county in ('Rush County','Toole County','Jefferson County','Dona Ana County','La Porte County') and  cd_demo_sk = c.c_current_cdemo_sk and   exists (select *          from store_sales,date_dim          where c.c_customer_sk = ss_customer_sk and                ss_sold_date_sk = d_date_sk and                d_year = 2002 and                d_moy between 1 and 1+3) and   (exists (select *            from web_sales,date_dim            where c.c_customer_sk = ws_bill_customer_sk and                  ws_sold_date_sk = d_date_sk and                  d_year = 2002 and                  d_moy between 1 ANd 1+3) or     exists (select *             from catalog_sales,date_dim            where c.c_customer_sk = cs_ship_customer_sk and                  cs_sold_date_sk = d_date_sk and                  d_year = 2002 and                  d_moy between 1 and 1+3)) group by cd_gender,          cd_marital_status,          cd_education_status,          cd_purchase_estimate,          cd_credit_rating,          cd_dep_count,          cd_dep_employed_count,          cd_dep_college_count order by cd_gender,          cd_marital_status,          cd_education_status,          cd_purchase_estimate,          cd_credit_rating,          cd_dep_count,          cd_dep_employed_count,          cd_dep_college_countlimit 100;


以下是对这条 SQL 查询语句的详细解释：
### 1. 查询目的这条 SQL 语句的主要目的是从多个相关数据表中检索特定客户群体的一些统计信息，并按照指定的字段进行分组、排序，最后限制结果集只返回前 100 条记录。它旨在分析在 2002 年特定时间段内有过购买行为（通过不同销售渠道）的客户在性别、婚姻状况、教育程度等方面的一些数据分布情况，并统计不同维度下的记录数量。
### 2. 查询涉及的表- **`customer`**：很可能存放着客户的基本信息，是本次查询的核心数据表之一，后续通过关联其他表来获取更全面的客户相关数据。- **`customer_address`**：用于存储客户地址相关的信息，通过地址相关字段（如 `c_current_addr_sk` 和 `ca_address_sk` 的关联）与 `customer` 表建立联系。- **`customer_demographics`**：包含客户的人口统计特征方面的数据，像性别、婚姻状况、教育程度等，通过 `cd_demo_sk` 和 `c_current_cdemo_sk` 的关联与 `customer` 表关联起来。- **`store_sales`**：存放实体店销售相关的数据，用于判断客户是否在实体店有符合条件的购买记录。- **`date_dim`**：一般是一个日期维度表，提供日期相关的各种属性（如年、月等），用于和销售表关联来筛选特定时间段的销售记录。- **`web_sales`**：记录网络销售情况的数据表，用来判断客户在网络渠道的购买行为是否符合条件。- **`catalog_sales`**：针对目录销售的相关数据表，同样用于核查客户在目录销售渠道的购买情况。
### 3. 筛选条件（`WHERE` 子句部分）- **表关联条件**：    - `c.c_current_addr_sk = ca.ca_address_sk`：将 `customer` 表和 `customer_address` 表通过地址相关的字段进行关联，以获取客户对应的地址信息。    - `cd_demo_sk = c.c_current_cdemo_sk`：把 `customer_demographics` 表和 `customer` 表关联起来，使得能获取客户的人口统计特征数据。- **地址筛选条件**：`ca_county in ('Rush County','Toole County','Jefferson County','Dona Ana County','La Porte County')`，限定了只选取来自特定几个县的客户相关记录，缩小了查询范围。- **购买行为存在性判断条件（通过子查询和 `EXISTS` 关键字实现）**：    - 第一个 `EXISTS` 子查询：        - `exists (select * from store_sales,date_dim where c.c_customer_sk = ss_customer_sk and ss_sold_date_sk = d_date_sk and d_year = 2002 and d_moy between 1 and 1 + 3)`：这个子查询用于判断是否存在客户在 2002 年的第 1 月到第 4 月（`d_moy between 1 and 1 + 3`，这里 `d_moy` 表示月份，就是筛选出 1 到 4 月的记录）在实体店（通过关联 `store_sales` 表）有购买行为（通过关联 `date_dim` 表来确定时间）。只有满足这个条件的客户记录才会被进一步考虑在主查询中。    - 第二个 `EXISTS` 子查询组合（通过 `OR` 连接）：        - `exists (select * from web_sales,date_dim where c.c_customer_sk = ws_bill_customer_sk and ws_sold_date_sk = d_date_sk and d_year = 2002 and d_moy between 1 ANd 1 + 3)`：判断客户在 2002 年 1 到 4 月是否在网络销售渠道有购买行为，关联了 `web_sales` 表和 `date_dim` 表来做此判断。        - `exists (select * from catalog_sales,date_dim where c.c_customer_sk = cs_ship_customer_sk and cs_sold_date_sk = d_date_sk and d_year = 2002 and d_moy between 1 and 1 + 3)`：类似地，判断客户在 2002 年 1 到 4 月是否在目录销售渠道有购买行为，通过关联 `catalog_sales` 表和 `date_dim` 表实现。这里使用 `OR` 连接这两个子查询，表示只要客户在网络销售或者目录销售渠道有符合时间条件的购买行为，就满足筛选条件。
### 4. 选择的字段（`SELECT` 子句部分）- 选择了 `cd_gender`（客户性别）、`cd_marital_status`（客户婚姻状况）、`cd_education_status`（客户教育状况）、`cd_purchase_estimate`（可能是客户购买预估相关字段）、`cd_credit_rating`（客户信用评级）、`cd_dep_count`（可能和客户家属数量相关的字段）、`cd_dep_employed_count`（可能是家属中就业人数相关字段）、`cd_dep_college_count`（可能是家属中大学学历人数相关字段）这些来自 `customer_demographics` 表的字段，用于展示不同维度的客户特征信息。- 同时，对于每个分组情况，通过 `count(*)` 分别统计了不同的数量，使用了别名 `cnt1`、`cnt2`、`cnt3`、`cnt4`、`cnt5`、`cnt6`。这里 `count(*)` 表示统计满足当前分组条件下的记录行数，不同的别名对应不同位置上统计的数量，具体每个统计的含义要结合业务场景进一步明确，但大致是在不同维度组合下做记录数统计。
### 5. 分组（`GROUP BY` 子句部分）按照 `cd_gender`、`cd_marital_status`、`cd_education_status`、`cd_purchase_estimate`、`cd_credit_rating`、`cd_dep_count`、`cd_dep_employed_count`、`cd_dep_college_count` 这些字段进行分组，意味着会将数据按照这些字段值的不同组合进行归类，然后针对每一组进行聚合统计（如 `count(*)` 的统计操作）。
### 6. 排序（`ORDER BY` 子句部分）按照和分组同样的字段顺序，即 `cd_gender`、`cd_marital_status`、`cd_education_status`、`cd_purchase_estimate`、`cd_credit_rating`、`cd_dep_count`、`cd_dep_employed_count`、`cd_dep_college_count` 对查询结果进行排序，使得结果集在展示时按照这些字段的顺序依次排列，方便查看和分析数据在不同维度下的分布情况。
### 7. 结果集限制（`LIMIT` 子句部分）使用 `LIMIT 100`，规定最终返回的查询结果最多只显示 100 条记录，避免返回过多数据，方便查看和处理最前面的部分数据内容。
总的来说，这条 SQL 语句通过多表关联、条件筛选、分组聚合、排序以及结果限制等操作，从多个数据表中提取和分析特定客户群体在特定时间段、特定购买行为下的多维度统计信息。 

with year_total as ( select c_customer_id customer_id       ,c_first_name customer_first_name       ,c_last_name customer_last_name       ,c_preferred_cust_flag customer_preferred_cust_flag       ,c_birth_country customer_birth_country       ,c_login customer_login       ,c_email_address customer_email_address       ,d_year dyear       ,sum(ss_ext_list_price-ss_ext_discount_amt) year_total       ,'s' sale_type from customer     ,store_sales     ,date_dim where c_customer_sk = ss_customer_sk   and ss_sold_date_sk = d_date_sk group by c_customer_id         ,c_first_name         ,c_last_name         ,c_preferred_cust_flag          ,c_birth_country         ,c_login         ,c_email_address         ,d_year  union all select c_customer_id customer_id       ,c_first_name customer_first_name       ,c_last_name customer_last_name       ,c_preferred_cust_flag customer_preferred_cust_flag       ,c_birth_country customer_birth_country       ,c_login customer_login       ,c_email_address customer_email_address       ,d_year dyear       ,sum(ws_ext_list_price-ws_ext_discount_amt) year_total       ,'w' sale_type from customer     ,web_sales     ,date_dim where c_customer_sk = ws_bill_customer_sk   and ws_sold_date_sk = d_date_sk group by c_customer_id         ,c_first_name         ,c_last_name         ,c_preferred_cust_flag          ,c_birth_country         ,c_login         ,c_email_address         ,d_year         )  select                    t_s_secyear.customer_id                 ,t_s_secyear.customer_first_name                 ,t_s_secyear.customer_last_name                 ,t_s_secyear.customer_preferred_cust_flag from year_total t_s_firstyear     ,year_total t_s_secyear     ,year_total t_w_firstyear     ,year_total t_w_secyear where t_s_secyear.customer_id = t_s_firstyear.customer_id         and t_s_firstyear.customer_id = t_w_secyear.customer_id         and t_s_firstyear.customer_id = t_w_firstyear.customer_id         and t_s_firstyear.sale_type = 's'         and t_w_firstyear.sale_type = 'w'         and t_s_secyear.sale_type = 's'         and t_w_secyear.sale_type = 'w'         and t_s_firstyear.dyear = 2001         and t_s_secyear.dyear = 2001+1         and t_w_firstyear.dyear = 2001         and t_w_secyear.dyear = 2001+1         and t_s_firstyear.year_total > 0         and t_w_firstyear.year_total > 0         and case when t_w_firstyear.year_total > 0 then t_w_secyear.year_total / t_w_firstyear.year_total else 0.0 end             > case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else 0.0 end order by t_s_secyear.customer_id         ,t_s_secyear.customer_first_name         ,t_s_secyear.customer_last_name	 ,t_s_secyear.customer_preferred_cust_flaglimit 100;

以下是对这条较为复杂的 SQL 查询语句的详细解释：
### 1. 整体结构与目的这条 SQL 语句整体上是先通过公共表达式（CTE，Common Table Expression，这里使用 `WITH` 关键字定义）构建了一个名为 `year_total` 的临时结果集，然后基于这个临时结果集进行多表关联、筛选以及排序等操作，最终目的是从客户销售数据中找出满足特定条件的客户记录，并按照一定规则排序后返回前 100 条记录。它主要用于对比分析不同销售渠道（实体店销售和网络销售）下客户在不同年份的销售金额变化情况，筛选出符合增长比例条件的客户信息。
### 2. `year_total` 公共表达式（CTE）部分- **第一个 `SELECT` 语句（实体店销售相关）**：    - **选择的字段**：        - 从 `customer` 表选取了 `c_customer_id`（客户 ID）、`c_first_name`（客户名字）、`c_last_name`（客户姓氏）、`c_preferred_cust_flag`（客户是否为优选客户标志）、`c_birth_country`（客户出生国家）、`c_login`（客户登录名）、`c_email_address`（客户邮箱地址）这些字段，同时选取了 `date_dim` 表中的 `d_year`（年份）字段，另外通过计算 `sum(ss_ext_list_price - ss_ext_discount_amt)` 得出每个客户在对应年份的实体店销售总金额，并起别名为 `year_total`，还定义了一个常量 `'s'` 作为 `sale_type`（表示销售类型为实体店销售）。    - **表关联与分组条件**：        - 通过 `c_customer_sk = ss_customer_sk` 和 `ss_sold_date_sk = d_date_sk` 分别将 `customer` 表、`store_sales` 表以及 `date_dim` 表进行关联，确保能获取到正确的客户、对应销售记录以及销售日期相关信息。然后按照选取的多个客户相关字段以及 `d_year` 字段进行分组，这样就可以针对每个客户在每年分别统计其实体店销售总金额。- **`UNION ALL` 操作**：将上述从实体店销售数据统计的结果与下面从网络销售数据统计的结果合并在一起，不会去除重复行（如果有重复的话），形成一个包含实体店销售和网络销售两种渠道数据的综合临时结果集（也就是 `year_total`）。- **第二个 `SELECT` 语句（网络销售相关）**：    - **选择的字段**：结构与前面实体店销售相关的 `SELECT` 语句类似，同样选取了多个客户相关字段以及 `date_dim` 表的 `d_year` 字段，不过这里计算网络销售总金额的方式是 `sum(ws_ext_list_price - ws_ext_discount_amt)`，并同样定义 `sale_type` 为 `'w'`，表示网络销售类型。    - **表关联与分组条件**：通过 `c_customer_sk = ws_bill_customer_sk` 和 `ws_sold_date_sk = d_date_sk` 将 `customer` 表、`web_sales` 表以及 `date_dim` 表进行关联，然后按照相应字段进行分组，以统计每个客户每年的网络销售总金额。
### 3. 主查询部分（外层 `SELECT` 语句）- **选择的字段**：从临时结果集 `year_total` 中选取了部分客户相关的字段，包括 `customer_id`、`customer_first_name`、`customer_last_name`、`customer_preferred_cust_flag`，用于展示最终符合条件的客户基本信息。- **表关联条件**：    - 将 `year_total` 这个临时结果集进行多次自关联，分别起了别名 `t_s_firstyear`（可理解为实体店销售第一年相关数据）、`t_s_secyear`（实体店销售第二年相关数据）、`t_w_firstyear`（网络销售第一年相关数据）、`t_w_secyear`（网络销售第二年相关数据）。通过一系列相等条件关联这些别名表，如 `t_s_secyear.customer_id = t_s_firstyear.customer_id` 等，确保所选取的数据是针对同一个客户在不同销售渠道、不同年份的记录。- **筛选条件**：    - **销售类型筛选**：限定了 `t_s_firstyear.sale_type = 's'`、`t_w_firstyear.sale_type = 'w'`、`t_s_secyear.sale_type = 's'`、`t_w_secyear.sale_type = 'w'`，明确区分了不同别名表对应的是实体店销售还是网络销售类型。    - **年份筛选**：设置了 `t_s_firstyear.dyear = 2001`、`t_s_secyear.dyear = 2001 + 1`（即 2002 年）、`t_w_firstyear.dyear = 2001`、`t_w_secyear.dyear = 2001 + 1`，这样就聚焦在了 2001 年（作为第一年）和 2002 年（作为第二年）的数据对比上，查看客户在这两年间不同销售渠道的销售金额变化情况。    - **销售金额大于 0 筛选**：通过 `t_s_firstyear.year_total > 0` 和 `t_w_firstyear.year_total > 0` 要求客户在 2001 年不管是实体店销售还是网络销售渠道，都要有大于 0 的销售金额，排除那些没有产生销售的客户记录。    - **销售金额增长比例筛选（通过 `CASE WHEN` 语句实现）**：        - `case when t_w_firstyear.year_total > 0 then t_w_secyear.year_total / t_w_firstyear.year_total else 0.0 end > case when t_s_firstyear.year_total > 0 then t_s_secyear.year_total / t_s_firstyear.year_total else 0.0 end` 这个条件比较复杂，它的核心是分别计算网络销售和实体店销售渠道下客户从 2001 年到 2002 年销售金额的增长比例（如果第一年销售金额大于 0 则进行计算，否则赋值为 0.0），然后筛选出网络销售增长比例大于实体店销售增长比例的客户记录。- **排序条件**：按照 `t_s_secyear.customer_id`、`t_s_secyear.customer_first_name`、`t_s_secyear.customer_last_name`、`t_s_secyear.customer_preferred_cust_flag` 这些字段对结果进行排序，使得结果集呈现出有规律的排列，方便查看符合条件的客户信息。- **结果集限制**：使用 `LIMIT 100`，只返回前 100 条满足上述所有条件的记录，避免返回过多数据。
总的来说，这条 SQL 语句先是综合统计了不同销售渠道下客户每年的销售金额情况，然后通过复杂的关联、筛选条件找出在特定年份内网络销售增长比例优于实体店销售增长比例的客户，并展示相关客户基本信息，限制只返回前 100 条这样的记录用于进一步分析。 

select  i_item_id      ,i_item_desc      ,i_category      ,i_class      ,i_current_price      ,sum(ws_ext_sales_price) as itemrevenue      ,sum(ws_ext_sales_price)*100/sum(sum(ws_ext_sales_price)) over          (partition by i_class) as revenueratiofrom        web_sales        ,item        ,date_dimwhere        ws_item_sk = i_item_sk        and i_category in ('Sports', 'Books', 'Home')        and ws_sold_date_sk = d_date_sk        and d_date between cast('1999-02-22' as date)                                and (cast('1999-02-22' as date) + INTERVAL '30' DAY)group by        i_item_id        ,i_item_desc        ,i_category        ,i_class        ,i_current_priceorder by        i_category        ,i_class        ,i_item_id        ,i_item_desc        ,revenueratiolimit 100;