CREATE TABLE cds.new_cust ( `window_start` DateTime, `window_end` DateTime, `new_cust_num` UInt32, `ts` UInt64 ) ENGINE = ReplacingMergeTree(ts) PARTITION BY toYYYYMMDD(window_start) ORDER BY (window_start, window_end, new_cust_num) 这样一张表 如何按分区查询
时间: 2024-02-20 13:01:18 浏览: 23
按分区查询可以使用以下语句:
```
SELECT *
FROM cds.new_cust
WHERE toYYYYMMDD(window_start) = '20211231'
```
这将返回 `window_start` 字段日期为 2021 年 12 月 31 日的所有行。您可以将日期替换为所需的分区日期。请注意,这将只返回特定分区的数据,而不是整个表中的所有数据。
相关问题
select * from ( select row_.*, rownum rownum_ from ( select * from ( select distinct OB.BUSI_ORDER_ID, 0 as HIS_ID, OB.BUSI_CODE, OB.CUST_ID, OB.CEASE_REASON, OB.ORDER_STATE, OB.CHANNEL_TYPE, ob.user_id, OB.IS_BATCH_ORDER, OB.APPLICATION_ID, OB.CREATE_DATE, OB.DONE_DATE, OB.EFF_DATE, OB.EXP_DATE, OB.OPER_ID, OB.ORG_ID, OB.REGION_ID, OB.NOTE, OB.PROCESS_STATE, nvl(oi.cust_name, ic.cust_name) cust_name, nvl(oc.icc_id, iu.icc_id) icc_id, nvl(oc.svc_num, iu.svc_num) svc_num, icp.cust_name parent_cust_name, icp.cust_id parent_cust_id, ol.order_list_id from ord_busi ob left join ord_offer oo on oo.busi_order_id = ob.busi_order_id and ob.user_id = oo.user_id left join info_user iu on oo.user_id = iu.user_id left join info_cust ic on ob.cust_id = ic.cust_id left join ord_cust oi on ob.cust_id = oi.cust_id and ob.busi_order_id = oi.busi_order_id left join info_cust icp on nvl(ic.parent_cust_id, oi.parent_cust_id) = icp.cust_id left join ( SELECT * FROM ord_user WHERE user_order_id IN ( SELECT MAX(user_order_id) user_order_id FROM ord_user GROUP BY busi_order_id,user_id ) ) oc on ob.user_id = oc.user_id and ob.busi_order_id = oc.busi_order_id left join ord_list ol on ob.busi_order_id = ol.busi_order_id WHERE 1 = 1 and OB.CUST_ID IN( SELECT DISTINCT CUST_ID FROM (SELECT CUST_ID, PARENT_CUST_ID FROM INFO_CUST UNION SELECT CUST_ID, PARENT_CUST_ID FROM ORD_CUST) TMP START WITH TMP.CUST_ID = '10001009208' CONNECT BY TMP.PARENT_CUST_ID = PRIOR TMP.CUST_ID ) union all select distinct OB.BUSI_ORDER_ID,OB.HIS_ID, OB.BUSI_CODE, OB.CUST_ID, OB.CEASE_REASON, OB.ORDER_STATE, OB.CHANNEL_TYPE, OB.USER_ID, OB.IS_BATCH_ORDER, OB.APPLICATION_ID, OB.CREATE_DATE, OB.DONE_DATE, OB.EFF_DATE, OB.EXP_DATE, OB.OPER_ID, OB.ORG_ID, OB.REGION_ID, OB.NOTE, OB.PROCESS_STATE, nvl(oi.cust_name,ic.cust_name) cust_name , nvl(oc.icc_id, iu.icc_id) icc_id, nvl(oc.svc_num,iu.svc_num) svc_num, icp.cust_name parent_cust_name, icp.cust_id parent_cust_id, ol.order_list_id from ord_busi_his ob left join ord_offer_his oo on oo.busi_order_id = ob.busi_order_id 优化一下
可以考虑以下优化方式:
1. 使用INNER JOIN代替LEFT JOIN:如果确保两个表中都含有匹配的行,则使用INNER JOIN可以提高查询效率。
2. 使用EXISTS代替IN:EXISTS执行速度比IN更快,因为它只需要找到匹配的第一个结果,而不是找到所有匹配的结果。
3. 简化子查询:在查询中可以尝试简化子查询,以减少查询的复杂度。
4. 使用索引:使用索引可以提高查询效率,可以根据查询中经常使用的列添加索引。
5. 避免使用SELECT *:只查询需要的列,避免使用SELECT *可以减少查询的复杂度,提高查询效率。
翻译以下代码everyone_guest_time_daily_show_pref C INNER JOIN ( SELECT COD_CUST_ID,COD_CLERK_ID,BAL_BOOK_DAILY_0 FROM everyone_guest_time_daily_show_pref WHERE ETL_LOAD_TIME = ? AND BAL_BM_TYPE_0 = 0) F ON C.COD_CUST_ID = F.COD_CUST_ID AND C.COD_CLERK_ID = F.COD_CLERK_ID SET C.BAL_BOOK_DAILY_REAL_0 = F.BAL_BOOK_DAILY_0 WHERE C.ETL_LOAD_TIME = ?
这段代码是一个 SQL 查询语句,它执行了一个内连接操作。下面是该代码的翻译:
```
将 everyone_guest_time_daily_show_pref 表与子查询 (SELECT COD_CUST_ID,COD_CLERK_ID,BAL_BOOK_DAILY_0 FROM everyone_guest_time_daily_show_pref WHERE ETL_LOAD_TIME = ? AND BAL_BM_TYPE_0 = 0) 的结果进行内连接,连接条件为 C.COD_CUST_ID = F.COD_CUST_ID 和 C.COD_CLERK_ID = F.COD_CLERK_ID。然后,将 C.BAL_BOOK_DAILY_REAL_0 的值设置为 F.BAL_BOOK_DAILY_0,其中 C.ETL_LOAD_TIME = ?。
```
请注意,代码中的 `?` 是占位符,具体的值需要根据实际情况进行替换。
相关推荐
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![pdf](https://img-home.csdnimg.cn/images/20210720083512.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)