insert table cdata_safe_income_new_day_analysis_item_status with t1 as ( select if(a.app_id is null,b.app_id,a.app_id) as app_id, if(a.product is null,b.product,a.product) as product, if(a.item_code_name is null,b.item_code_name,a.item_code_name) as item_code_name, case when a.app_id is null and (if(a.tiaozhang_cnt is null,0,a.tiaozhang_cnt) + if(b.tiaozhang_cnt is null,0,b.tiaozhang_cnt)) > 0 then '调账影响' when b.app_id is null and (if(a.tiaozhang_cnt is null,0,a.tiaozhang_cnt) + if(b.tiaozhang_cnt is null,0,b.tiaozhang_cnt)) > 0 then '调账影响' when a.app_id is null and (if(a.tuifei_cnt is null,0,a.tuifei_cnt) + if(b.tuifei_cnt is null,0,b.tuifei_cnt)) > 0 then '退费影响' when b.app_id is null and (if(a.tuifei_cnt is null,0,a.tuifei_cnt) + if(b.tuifei_cnt is null,0,b.tuifei_cnt)) > 0 then '退费影响' when a.app_id is null and c.voucher_begin > 0 then '优惠券影响' --- 原：续费使用了代金券 when a.app_id is null and c.voucher_end > 0 and change_order_cnt = 0 then '优惠券影响' --- 原：续费使用了代金券 when b.app_id is null and c.voucher_begin > 0 then '优惠券影响' --- 原：续费由代金券转为正常 when b.app_id is null and c.voucher_end > 0 and change_order_cnt = 0 then '优惠券影响' --- 原：续费由代金券转为正常 else 'item维度正常' end flag_status from (select app_id, product,item_code_name,sum(if(paymode_type = '调账',1,0)) as tiaozhang_cnt, sum(if(instr(paymode,'退费')>0,1,0)) as tuifei_cnt from cdata_safe_income_new_day where ftime = '${YYYYMMDD}' and paymode != '0元订单' and prod_class1_name = '基础安全' and prod_class2_name = '云原生安全' and product is not null and pay_type = '公有云' group by app_id,product,item_code_name ) a full join

时间: 2024-02-15 17:19:39 浏览: 158
(select app_id, product,item_code_name,sum(if(paymode_type = '调账',1,0)) as tiaozhang_cnt, sum(if(instr(paymode,'退费')>0,1,0)) as tuifei_cnt from cdata_safe_income_new_day where ftime = '${YYYYMMDD}' and paymode != '0元订单' and prod_class1_name = '基础安全' and prod_class2_name = '云原生安全' and product is not null and pay_type = '公有云' group by app_id,product,item_code_name ) b on a.app_id = b.app_id and a.product = b.product and a.item_code_name = b.item_code_name left join (select app_id, product,item_code_name, sum(if(instr(paymode,'优惠')>0,1,0)) as voucher_begin, sum(if(instr(paymode,'优惠')>0,1,0)) as voucher_end, sum(if(change_order_cnt > 0,1,0)) as change_order_cnt from cdata_safe_income_new_day where ftime = '${YYYYMMDD}' and paymode != '0元订单' and prod_class1_name = '基础安全' and prod_class2_name = '云原生安全' and product is not null and pay_type = '公有云' group by app_id,product,item_code_name) c on a.app_id = c.app_id and a.product = c.product and a.item_code_name = c.item_code_name; commit;
阅读全文

相关推荐

rar

Cdata-structure.rar_visual c

本资料“Cdata-structure.rar”聚焦于C语言的数据结构，并结合Visual C++进行实践，非常适合大学低年级学生作为学习编程的起点。 数据结构是计算机科学中的核心概念，它研究如何组织和存储数据，以便更有效地进行...
rar

SQL.rar_MyBatis3DynamicSql_dynamic mybatis_mybatis_mybatis Dyna

<select id="selectUsers" parameterType="map" resultType="User"> SELECT * FROM users <if test="name != null"> AND name LIKE #{name} </if> <if test="age != null"> AND age <![CDATA[>=]]> #{age} ...
rar

i2c_bus.rar_DM642_DM642 汇编_ccs i2c_i2c.rar 7640

1. 初始化I2C模块：在汇编代码中，我们需要设置I2C模块的相关寄存器，例如控制寄存器（I2CCTL）、数据寄存器（I2CDATA）、状态寄存器（I2CSTAT）等，以确定I2C的工作模式（主模式或从模式）、时钟频率、启动/停止...

ALTER TABLE cdata_safe_income_new_day_analysis_item_status DROP PARTITION (ds_${YYYYMMDD});ALTER TABLE cdata_safe_income_new_day_analysis_item_status ADD PARTITION ds_${YYYYMMDD} VALUES IN ( ${YYYYMMDD} ) ;

这段代码是用来修改表 cdata_safe_income_new_day_analysis_item_status 的分区。具体来说，它执行了两个操作： 1. ALTER TABLE cdata_safe_income_new_day_analysis_item_status DROP PARTITION (ds_${YYYYMMDD...

解释一下这段代码：CREATE TABLE if not exists cdata_safe_income_new_day_analysis_item_status( app_id STRING COMMENT 'appid', product string comment '产品', item_code_name string comment 'item细项', flag_status string comment '状态', stat_date string) PARTITION BY LIST( stat_date ) (PARTITION ds_${YYYYMMDD} VALUES IN ( ${YYYYMMDD} ),PARTITION default);

这段代码是用来创建一个名为 cdata_safe_income_new_day_analysis_item_status 的表。它包含了以下列： - app_id：类型为字符串，表示应用ID。 - product：类型为字符串，表示产品。 - item_code_name：...

WITH t AS ( SELECT NVL(t2.FINANCIER,t2.ISSUER) customerId, NVL(NVL(t1.TDY_FLOAT_INGPL, 0) + NVL(t1.DSC_COST_AMT, 0),0) AS amount FROM PTL_SEC_VALUTION t1 INNER JOIN FIN_PRODUCT t2 ON t1.FINPROD_ID=t2.FINPROD_ID INNER JOIN FIN_PRODUCT_TYPE t3 ON t3.FINPROD_ID=t2.FINPROD_ID WHERE (t2.FINPROD_TYPE2='F01' OR (t2.FINPROD_TYPE2 ='F18' and t3.TYPE_6='P01' and t3.TYPE_7='1209') OR (t2.FINPROD_TYPE2 ='F18' and t3.TYPE_6='P11' )) AND t1.CDATE =to_date(#{valDate,jdbcType=VARCHAR},'yyyy-MM-dd')-1 ) select h.counterId from ( select n.counterId,n.amount from ( select m.counterId,sum(amount) amount from ( select t5.customer_Id counterId,T.AMOUNT from t INNER JOIN MST_CUSTOMER_INFO_ADD t5 on t5.customer_id=t.customerId where t5.INDUSTRY!='10' and t5.PARENT_CUSTOMER_ID is null union all select t6.customer_Id counterId,T.AMOUNT from t INNER JOIN MST_CUSTOMER_INFO_ADD t5 on t5.customer_id=t.customerId INNER JOIN MST_CUSTOMER_INFO_ADD t6 on t6.customer_id=t5.PARENT_CUSTOMER_ID where t5.INDUSTRY!='10' ) m group by m.counterId ) n order by n.amount desc ) h where rownum<![CDATA[<]]>11 改成ignite写法

= '10' AND t5.PARENT_CUSTOMER_ID IS NULL " + " UNION ALL " + " SELECT t6.customer_Id counterId, T.AMOUNT " + " FROM t " + " INNER JOIN MST_CUSTOMER_INFO_ADD t5 ON t5.customer_id = t.customerId " +...

SELECT * FROM ( SELECT ci.*, CASE WHEN ci.stop_status = 1 THEN 3 WHEN ci.stop_status = 2 AND ci.is_class_time = 0 THEN 2 WHEN ci.stop_status = 2 AND ci.is_class_time = 1 AND CONVERT(VARCHAR (10), ci.class_start_time, 20) <![CDATA[ <= ]]> CONVERT(VARCHAR (10), getdate(), 20) THEN 2 ELSE 1 END AS classStatus, COALESCE(classUser.userNum,0) AS userNum, COALESCE(classCourse.courseNum,0) AS courseNum FROM class_info ci LEFT JOIN ( SELECT cu.class_id, COUNT(1) AS userNum FROM class_user cu INNER JOIN customer c ON c.id = cu.user_id WHERE cu.is_valid = 1 AND c.is_valid = 1 AND cu.is_clearance = 0 GROUP BY cu.class_id ) AS classUser ON classUser.class_id = ci.id LEFT JOIN ( SELECT class_id, COUNT(1) AS courseNum FROM class_course GROUP BY class_id ) AS classCourse ON classCourse.class_id = ci.id

SELECT cu.class_id, COUNT(1) AS userNum FROM class_user cu INNER JOIN customer c ON c.id = cu.user_id WHERE cu.is_valid = 1 AND c.is_valid = 1 AND cu.is_clearance = 0 GROUP BY cu.class_id ) AS...

SELECT fs.id AS fundId, temp.id AS ticketId FROM t_fund_settlement fs, (SELECT order_no, id, SUM(receivables) AS receivables FROM t_ticket WHERE order_type = 'issue_ticket' and fund_verification_status <![CDATA[ <> ]]> 1 GROUP BY id, order_no) temp where fs.order_type = 'issue_ticket' AND fs.order_no = temp.order_no AND fs.amount = temp.receivables and fs.verification_status <![CDATA[ <> ]]> 1 and fs.platform <![CDATA[ <> ]]> 'XC' UNION SELECT fs.id AS fundId, t.id AS ticketId FROM t_fund_settlement fs, t_ticket t where fs.order_type = 'issue_ticket' AND fs.amount = t.receivables and t.ticket_no_operate = fs.ticket_no and fs.verification_status <![CDATA[ <> ]]> 1 and t.fund_verification_status <![CDATA[ <> ]]> 1 and fs.platform = 'XC' LIMIT #{page} , 1000

然后，根据订单号和ID进行分组，并计算receivables字段的总和。这个子查询的结果被命名为temp。 第一个查询中的主查询部分通过连接t_fund_settlement和temp表来检索基金与票据的信息。它使用了多个条件来匹配订单...

SELECT T2.id, T2.org_type, T2.org_name FROM ( SELECT @r AS _id, (SELECT @r := p_id FROM t_organization WHERE id = _id) AS parent_id, @l := @l + 1 AS lvl FROM (SELECT @r := #{orgId}, @l := 0) vars, t_organization h WHERE @r <![CDATA[<>]]> 0) T1 JOIN t_organization T2 ON T1._id = T2.id where T2.org_type = '1' ORDER BY T2.org_type DESC limit 1翻译一下这段sql 加上注释

JOIN t_organization T2 ON T1._id = T2.id -- 将 T1 中的 _id 和 t_organization 表中的 id 进行 JOIN 操作 WHERE T2.org_type = '1' -- 过滤 org_type 字段等于 1 的记录 ORDER BY T2.org_type DESC -- 按照 org_...

select TRADE_REQUEST_ID tradeRequestId, INTERNAL_DEPARTMENT_CODE internalDepartmentCode, TP_PAY_ORGAN_ID tpPayOrganId, PAY_AMOUNT payAmount, FEE fee from( select orderInfo.TRADE_REQUEST_ID, orderInfo.INTERNAL_DEPARTMENT_CODE, orderInfo.TP_PAY_ORGAN_ID, orderInfo.PAY_AMOUNT, orderInfo.FEE, ROWNUM r from T_PAY_SYNC_RECONCILE_AC_TASK orderInfo where ORDERINFO.STATUS = '0' AND ORDERINFO.ORG_MER_DATE BETWEEN TRUNC(#startDate:DATE#)+ #syncStartDay:NUMBER# + #syncStartTime:NUMBER#/ (24) and TRUNC(#startDate:DATE#)+ #syncEndDay:NUMBER# + #syncEndTime:VARCHAR#/ (24) )table_a <![CDATA[ where r>#firstIndex:NUMBER# and r<=#endIndex:NUMBER# ]]> 优化这段SQL语句

) table_a WHERE ROWNUM > :firstIndex AND ROWNUM ; 请在实际使用中替换掉:startDate、:syncStartDay、:syncEndDay、:firstIndex和:endIndex这些绑定变量，并根据具体情况为相关列添加索引。

以下sql存在多条一样的general_master_type_nm数据，怎么修改呢？ WITH -- 表示権限区分が0の汎用マスタ区分を洗い出す a1 AS ( SELECT DISTINCT GENERAL_MASTER_TYPE, DISPLAY_AUTH_CLS AS displayAuthCls FROM m_general WHERE DISPLAY_AUTH_CLS = '0' ), -- 更新権限区分が0の汎用マスタ区分を洗い出す a2 AS ( SELECT DISTINCT GENERAL_MASTER_TYPE, UPD_AUTH_CLS AS updAuthCls FROM m_general WHERE UPD_AUTH_CLS = '0' ), b AS ( SELECT GENERAL_MASTER_TYPE, GENERAL_MASTER_TYPE_NM, MIN( DISPLAY_AUTH_CLS ) AS displayAuthCls, MIN( UPD_AUTH_CLS ) AS updAuthCls FROM m_general GROUP BY GENERAL_MASTER_TYPE, GENERAL_MASTER_TYPE_NM ),-- a と bを合併した最小表示権限区分 c AS ( SELECT b.GENERAL_MASTER_TYPE, b.GENERAL_MASTER_TYPE_NM, IFNULL ( a1.displayAuthCls, b.displayAuthCls ) AS displayAuthCls, IFNULL ( a2.updAuthCls, b.updAuthCls ) AS updAuthCls FROM b LEFT JOIN a1 ON b.GENERAL_MASTER_TYPE = a1.GENERAL_MASTER_TYPE LEFT JOIN a2 ON b.GENERAL_MASTER_TYPE = a2.GENERAL_MASTER_TYPE ) -- 抽出結果 SELECT DISTINCT (mg.GENERAL_MASTER_TYPE), mg.GENERAL_MASTER_TYPE_NM, c.updAuthCls, c.displayAuthCls, #{authority.slpmAuthCls} as slpmAuthCls FROM m_general mg INNER JOIN c ON mg.GENERAL_MASTER_TYPE = c.GENERAL_MASTER_TYPE AND (c.displayAuthCls <![CDATA[ <> '0']]> OR ( #{authority.slpmAuthCls} = '0' AND #{authority.levelType} ='1') ) WHERE 1 = 1 <if test="!containsDel"> and mg.del_flg <![CDATA[ <> '1']]> </if> ORDER BY mg.GENERAL_MASTER_TYPE_NM ASC </select>

可以使用UPDATE语句来修改数据库中的数据。可以使用GROUP BY语句来将重复的数据进行合并，使用MAX或MIN函数选择其中一个来保留一个唯一的值。例如，可以这样编写一个UPDATE语句： UPDATE m_general ...

select employee_number, card_number FROM ( SELECT id, employee_number, card_number FROM ( SELECT case when LENGTH(pin) = 5 THEN CONCAT('000', pin) ELSE pin end as employee_number, cardno as card_number, '' AS id from gate_staff union ALL SELECT employee_number, card_number, id FROM gate_mdm_dm_cardinfo WHERE id in ( SELECT max(id) as maxId FROM gate_mdm_dm_cardinfo a INNER JOIN ( SELECT employee_number, MAX(last_update_time) max_date FROM gate_mdm_dm_cardinfo WHERE last_update_time <![CDATA[ >= ]]> #{startTime} AND last_update_time <![CDATA[ <= ]]> #{endTime} GROUP BY employee_number ) b ON a.employee_number = b.employee_number AND a.last_update_time = b.max_date GROUP BY a.employee_number) and status = 1 and public_flag = 'Y' ) as mid group by employee_number, card_number having count(*) = 1 ) AS ccc WHERE ccc.id != ''; 优化这段代码

WHERE a.status = 1 AND a.public_flag = 'Y' ) AS mid GROUP BY employee_number, card_number HAVING COUNT(*) = 1 ) AS ccc WHERE ccc.id != ''; 2. 添加索引：根据查询中涉及的字段，在gate_staff表和...

<select id="loadArticleList" resultMap="resultMap"> SELECT aa.* FROM ap_article aa LEFT JOIN ap_article_config aac ON aa.id = aac.article_id <where> and aac.is_delete != 1 and aac.is_down != 1 <if test="type != null and type == 1"> and aa.publish_time <![CDATA[<]]> #{dto.minBehotTime} </if> <if test="type != null and type == 2"> and aa.publish_time <![CDATA[>]]> #{dto.maxBehotTime} </if> <if test="dto.tag != '__all__'"> and aa.channel_id = #{dto.tag} </if> </where> order by aa.publish_time desc limit #{dto.size} </select>

4. <if>语句块中的条件根据dto.tag的值来筛选特定标签的文章，如果dto.tag不等于'__all__'，则只选择对应标签的文章。 查询结果按照发布时间(aa.publish_time)倒序排序，并限制返回结果数量为dto.size。 ...

优化sql语句，select distinct ce.emailid,ce.sender,ccc.client_id,'99' as query_tag,ccc.client_name as comany_name,ccc.clue_name as contact_name, case when ccc.ascription_user = #{userid} then '1' else '2' end as chart_information from crm_email ce,crm_client_clue ccc where (ce.sender = ccc.clue_email or ce.content like concat('%', ccc.clue_email, '%') ) and ccc.clue_email is not null and ccc.clue_email <![CDATA[ <> ]]> '' and ce.type in ('2','3') and ccc.clue_status in ('0','1','2','3','9') and ccc.date_flag = '0' and ce.emailid = #{emailId}

SELECT DISTINCT ce.emailid, ce.sender, ccc.client_id, '99' AS query_tag, ccc.client_name AS company_name, ccc.clue_name AS contact_name, CASE WHEN ccc.ascription_user = #{userid} THEN '1' ELSE '2' ...

SELECT aa.* FROM ap_article aa LEFT JOIN ap_article_config aac ON aa.id = aac.article_id <where> and aac.is_delete != 1 and aac.is_down != 1 <if test="type != null and type == 1"> and aa.publish_time <![CDATA[<]]> #{dto.minBehotTime} </if> <if test="type != null and type == 2"> and aa.publish_time <![CDATA[>]]> #{dto.maxBehotTime} </if> <if test="dto.tag != '__all__'"> and aa.channel_id = #{dto.tag} </if> </where> order by aa.publish_time desc limit #{dto.size} </select>大致意思

3. 在条件语句中，排除了 ap_article_config 表中 is_delete 字段值为 1 和 is_down 字段值为 1 的记录。 4. 根据不同的条件，动态生成了三个条件子句： - 如果 type 不为空且等于 1，则添加一个条件，要求...

(random.randint(0, bs_data[12]-1))*3+bs_data[8] 报错：Traceback (most recent call last): File "C:\Users\z84259074\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\core\indexes\base.py", line 3652, in get_loc return self._engine.get_loc(casted_key) File "pandas\_libs\index.pyx", line 147, in pandas._libs.index.IndexEngine.get_loc File "pandas\_libs\index.pyx", line 176, in pandas._libs.index.IndexEngine.get_loc File "pandas\_libs\hashtable_class_helper.pxi", line 7080, in pandas._libs.hashtable.PyObjectHashTable.get_item File "pandas\_libs\hashtable_class_helper.pxi", line 7088, in pandas._libs.hashtable.PyObjectHashTable.get_item KeyError: 12 The above exception was the direct cause of the following exception: Traceback (most recent call last): File "d:\Users\z84259074\PycharmProjects\参数自优化\self_optimizing.py", line 128, in <module> data = optimizing() File "d:\Users\z84259074\PycharmProjects\参数自优化\self_optimizing.py", line 15, in __init__ self.optimizing_main() File "d:\Users\z84259074\PycharmProjects\参数自优化\self_optimizing.py", line 124, in optimizing_main self.child2=self.mutation_cdata(fitness_data,self.cross_data) File "d:\Users\z84259074\PycharmProjects\参数自优化\self_optimizing.py", line 92, in mutation_cdata print('cross_data[波束场景No]',bs_data[12]) File "C:\Users\z84259074\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\core\frame.py", line 3761, in __getitem__ indexer = self.columns.get_loc(key) File "C:\Users\z84259074\AppData\Local\Programs\Python\Python310\lib\site-packages\pandas\core\indexes\base.py", line 3654, in get_loc raise KeyError(key) from err KeyError: 12

根据错误信息，看起来是你试图从一个Pandas DataFrame中获取列索引为12的数据，但是该列索引不存在导致出现了KeyError异常。 请确认一下你的DataFrame中是否存在列索引为12的列。你可以使用df.columns来查看...

select t.device_name,t.trigger_time,t.num,t.triggerDay,t.hday,IFNULL(w.num, 0) as withBCount from( select v.device_name,d.trigger_time,(case v.device_name when 1 then 1 when 6 then 2 when 4 then 3 when 5 then 4 when 3 then 5 when 2 then 6 end) as num, DATE_FORMAT(d.trigger_time,'%Y-%m-%d') as triggerDay,HOUR(d.trigger_time) as hday from ( <include refid="selectEventDeviceName"/> ) v cross join ( SELECT DATE_FORMAT(date_add(#{query.startTime}, INTERVAL t4+t3*10+t2*100+t1*1000 HOUR), '%Y-%m-%d %H:00:00') AS trigger_time FROM (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1, (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2, (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3, (SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4 WHERE DATE_FORMAT(date_add(#{query.startTime}, INTERVAL t4+t3*10+t2*100+t1*1000 HOUR), '%Y-%m-%d %H:00:00') <![CDATA[<]]> #{query.endTime} ) as d ) as t left join event_studio_withb_stat w on t.device_name=w.device_location and t.trigger_time=w.start_time

这段代码是一个 SQL 查询语句，用于查询设备事件的统计数据，包括设备名称、触发时间、事件编号、触发日期、触发小时、以及关联的 event_studio_withb_stat 表中的 withBCount 字段值。其中，selectEventDeviceName ...

SELECT count(*) FROM ccs_comp_claim t, ccs_loan t2WHERE t.ref_nbr = t2.ref_nbr AND t.finish_date is null AND t.cooperator_id = 'DYBANK' AND t2.union_insu_case_date <![CDATA[ < ]]> #{countDate,jdbcType=TIMESTAMP}，如上sql的countDate为参数，要实现union_insu_case_date小于countDate加1天

SELECT count(*) FROM ccs_comp_claim t, ccs_loan t2 WHERE t.ref_nbr = t2.ref_nbr AND t.finish_date is null AND t.cooperator_id = 'DYBANK' AND t2.union_insu_case_date (day, 1, #{countDate,jdbcType=...

SELECT count(*) FROM ccs_comp_claim t, ccs_loan t2WHERE t.ref_nbr = t2.ref_nbr AND t.finish_date is null AND t.cooperator_id = 'DYBANK' AND t2.union_insu_case_date <![CDATA[ < ]]> #{countDate,jdbcType=TIMESTAMP}，如上sql的countDate为参数，要实现union_insu_case_date加1天小于countDate

SELECT count(*) FROM ccs_comp_claim t, ccs_loan t2 WHERE t.ref_nbr = t2.ref_nbr AND t.finish_date is null AND t.cooperator_id = 'DYBANK' AND DATEADD(day, 1, t2.union_insu_case_date) ,jdbcType=...
none

无行为老化和绝对老化区别

无行为老化和绝对老化区别