自助健康检查常用命令
查询最近一天消耗比较高的Query select status as"状态",duration as"耗时(ms)",query_start as"开始时间",(read_bytes/1048576):text|' MB' as"读取量",(memory_bytes/1048576):text|' MB' as"内存",(shuffle_bytes/1048576):text|' MB' ...
Failed to parse response as json format
Failed to parse response as json format.Response:?xml version='1.0' encoding='UTF-8'?返回的格式是 xml，而期望返回是 json；在请求的时候可以设置参数 formatType 为 json。公共参数:Format 返回值的类型，支持 JSON 与 XML，默认为 ...
删除账号
SELECT s.srvname AS"Name",pg_catalog.pg_get_userbyid(s.srvowner)AS"Owner",f.fdwname AS"Foreign-data wrapper",pg_catalog.array_to_string(s.srvacl,E' ')AS"Access privileges",s.srvtype AS"Type",s.srvversion AS"Version",CASE ...
PS线性回归
create table lm_test_input as select*from(select cast(2 as BIGINT)as label,'1:0.55 2:0.15 3:0.82 4:0.99 5:0.17' as features union all select cast(1 as BIGINT)as label,'1:1.26 2:1.36 3:0.13 4:2.82 5:0.41' as features union ...
使用CloudLens for SLS监控Project资源配额
select Project,region,round(count_logstore/quota_logstore*100,3)as logstore_ratio from(SELECT A.id as Project,A.region as region,COALESCE(SUM(B.count_logstore),0)AS count_logstore,cast(json_extract(A.quota,'$.logstore')as ...
WAF日志告警配置案例
id,host,count_if(status>=200 and status)AS status_2XX,count_if(status>=300 and status)AS status_3XX,count_if(status>=400 and status)AS status_4XX,count_if(status>=500 and status)AS status_5XX,COUNT(*)AS countall FROM log ...
点聚类系数
create table NodeDensity_func_test_edge as select*from(select '1' as flow_out_id,'2' as flow_in_id union all select '1' as flow_out_id,'3' as flow_in_id union all select '1' as flow_out_id,'4' as flow_in_id union all ...
WAF日志告警配置案例
id,host,count_if(status>=200 and status)AS status_2XX,count_if(status>=300 and status)AS status_3XX,count_if(status>=400 and status)AS status_4XX,count_if(status>=500 and status)AS status_5XX,COUNT(*)AS countall FROM log ...
如何快速搭建U2I2I召回服务
'10005' AS item_id,1.07 AS score UNION SELECT '10002' AS trigger_id,'10006' AS item_id,0.98 AS score UNION SELECT '10003' AS trigger_id,'10007' AS item_id,0.87 AS score;写入标识分区 INSERT OVERWRITE TABLE aime_example_i2i ...
JSON数据列展开
结果：+-+-+|a|b|+-+-+|1|2|3|4|+-+-+SQL示例2：SELECT t.m AS_col1,t.n AS_col2 FROM(SELECT cast(json_extract('{"x":[{"a":1,"b":2},{"a":3,"b":4}]}','$.x')AS array)AS array_1,cast(json_extract('{"x":[{"a":5,"b":6},{"a":7,"b":8}...
k-Core
out_id,'3' as flow_in_id union all select '2' as flow_out_id,'4' as flow_in_id union all select '3' as flow_out_id,'4' as flow_in_id union all select '3' as flow_out_id,'5' as flow_in_id union all select '3' as flow_out_id...
分析Nginx访问日志
request_uri:"/url2"|select count(1)as pv,approx_distinct(remote_addr)as uv,histogram(method)as method_pv,histogram(status)as status_pv,histogram(user_agent)as user_agent_pv,avg(request_time)as avg_latency,max(request_time)...
查询改写功能
物化视图语句如下：CREATE MATERIALIZED VIEW mv0 REFRESH NEXT now()+interval 1 day ENABLE QUERY REWRITE AS SELECT l.returnflag,l.linestatus,SUM(l.extendedprice*(1-l.discount))AS sum_disc_price,count(*)AS count_order FROM ...
alicloud_service_catalog_product_as_end_users
DEPRECATED:It has been deprecated from version 1.197.0.Please use new datasource alicloud_service_catalog_end_user_products instead.This data source provides Service Catalog Product As End User available to the user.What ...
Top-N
select a,b,c from(select*,row_number()over(partition by a order by c)as rk from(select a+1 as a,b,c from MyTable))where rk;修改order by相关属性（排序字段和方向），该修改属于不兼容修改。原始SQL。select a,b,c from(select*,...
性能白皮书（Nexmark性能测试）
INSERT INTO discard_sink SELECT P.name,P.city,P.state,A.id FROM auction AS A INNER JOIN person AS P on A.seller=P.id WHERE A.category=10 and(P.state='OR' OR P.state='ID' OR P.state='CA');q4 CREATE TEMPORARY TABLE nexmark_...
CAST
cast(string as datetime)或 cast(datetime as string)：会采用默认的日期格式 yyyy-mm-dd hh:mi:ss。除此之外，cast还支持基本数据类型与JSON类型之间的相互转换，所支持的类型包括：JSON/STRING/BIGINT/INT/TINYINT/SMALLINT/DOUBLE/...
Window Top-N
select a,b,c,d,window_start from(select*,row_number()over(partition by b,window_start,window_end order by c)as rk from(select a,sum(b)as b,max(c)as c,min(d)as d,window_start,window_end from table(tumble(table MyTable,...
创建自定义告警规则
ModifySecurityGroupPolicy)|select"event.userIdentity.accountId"as account_id,"event.userIdentity.principalId"as ram_user_id,"event.eventName"as event_name,arbitrary("event.userIdentity.type")as user_type,arbitrary("event....
UNNEST子句
number:[49,50,45,47,50]查询和分析语句*|SELECT sum(a)AS sum FROM log,UNNEST(cast(json_parse(number)as array(bigint)))AS t(a)查询和分析结果 示例3 将 number 字段的值（array类型）展开为多行单列形式，并对各个值进行分组统计。...
WITH
WITH子句中定义的关系可以互相连接 WITH x AS(SELECT a FROM t),y AS(SELECT a AS b FROM x),z AS(SELECT b AS c FROM y)SELECT c FROM z;CTE执行优化 3.1.9.3及以上内核版本的集群支持使用CTE执行优化功能，该功能默认关闭，您可以通过...
慢查询诊断
SELECT(cpu_time_ms/1000):text|' s' AS"CPU时间",query_start AS"开始时间",query_end AS"结束时间",query_duration_ms AS"耗时(ms)",query_id AS"查询ID",query AS"查询SQL"FROM qmonitor.instance_slow_queries WHERE query_start>=now...
CREATE TABLE AS
CREATE TABLE AS 创建一个表，并且用由一个 SELECT 命令计算出来的数据填充该表。简介 CREATE TABLE AS 创建一个表，并且用由一个 SELECT 命令计算出来的数据填充该表。该表的列具有和 SELECT 的输出列相关的名称和数据类型（不过可以通过...
K近邻
create table pai_knn_test_input as select*from(select 1 as f0,2 as f1,'good' as class from dual union all select 1 as f0,3 as f1,'good' as class from dual union all select 1 as f0,4 as f1,'bad' as class from dual union all...
行列转换
行转列 方法一：通过CASE WHEN语句 SELECT username,max(CASE WHEN subject='语文' THEN score END)AS `语文`,max(CASE WHEN subject='数学' THEN score END)AS `数学`,max(CASE WHEN subject='英语' THEN score END)AS `英语` FROM pivot ...
Deduplication
select a,b,c from(select*,row_number()over(partition by a order by proctime asc)as rk from(select a,b,substring(c,1,5)as c from MyTable))where rk=1;不兼容的修改 新增、删除、修改partition by key或者partition by key涉及字段...
并行查询使用示例
tax))AS sum_charge,Avg(l_quantity)AS avg_qty,Avg(l_extendedprice)AS avg_price,Avg(l_discount)AS avg_disc,Count(*)AS count_order FROM lineitem WHERE l_shipdate,l_linestatus ORDER BY l_returnflag,l_linestatus;未开启并行查询...
CREATE TABLE AS
Hologres从V1.3.21版本开始，支持使用CREATE TABLE AS语句创建表，复制表结构的同时也可以选择复制数据。本文为您介绍在Hologres中CREATE TABLE AS的用法。背景信息 CREATE TABLE AS的功能为：创建一个与源表结构或者查询Query结果相同的新...
大数据AI公共数据集分析
time,12)AS BIGINT)>=8 AND CAST(SUBSTR(behavior_time,12)AS BIGINT)点-11点' WHEN CAST(SUBSTR(behavior_time,12)AS BIGINT)>=12 AND CAST(SUBSTR(behavior_time,12)AS BIGINT)点-15点' WHEN CAST(SUBSTR(behavior_time,12)AS BIGINT)>=...
创建源表
jsonParser='default' 示例：create table property(json varchar,jsonType varchar,gmtCreate as to_timestamp(cast(json_value(json,'$.gmtCreate')as bigint)),deviceName as json_value(json,'$.deviceName'),productKey as json_value...
PARSE_URL
PARSE_URL(url1,'REF')as var5,PARSE_URL(url1,'PROTOCOL')as var6,PARSE_URL(url1,'FILE')as var7,PARSE_URL(url1,'AUTHORITY')as var8,PARSE_URL(nullstr,'QUERY')as var9,PARSE_URL(url1,'USERINFO')as var10,PARSE_URL(nullstr,'QUERY'...
Quick BI数据集预览报错：null:INTERNAL:java.sql....
price` AS T_A19_3_,ALI_T_1_.`sku_sales_price` AS T_ADA_4_,ALI_T_1_.`sku_margin_rate` AS T_A48_5_,ALI_T_1_.`shop_no` AS T_A9A_6_,ALI_T_1_.`sku_real_price` AS T_AFC_7_,TO_CHAR(ALI_T_1_.`created_datetime`,'yyyy')AS T_A72_8_,...
Table2KV
create table test as select*from(select 0 as rowid,1 as col0,1.1 as col1,2 as col2 from dual union all select 1 as rowid,0 as col0,1.2 as col1,3 as col2 from dual union all select 2 as rowid,1 as col0,2.3 as col1,4 as col2...
创建作业
select cast(to_unixtime(time)as bigint)as time,'aiops' as entity,diff[1]as curr,diff[2]as prev,abs(diff[3]-1)as ratio,if(abs(diff[3]-1)>0.2,1,0)as status FROM(select time,ts_compare(metric,86400)as diff FROM(select date_...
TPC-H测试集
SQL1 select l_returnflag,l_linestatus,sum(l_quantity)as sum_qty,sum(l_extendedprice)as sum_base_price,sum(l_extendedprice*(1-l_discount))as sum_disc_price,sum(l_extendedprice*(1-l_discount)*(1+l_tax))as sum_charge,avg(l_...
CREATE TABLE AS SELECT（CTAS）
云原生数据仓库AnalyticDB MySQL版 支持通过 CREATE TABLE 创建表，也支持通过 CREATE TABLE AS SELECT（CTAS）将查询到的数据写入新表中。语法 CREATE TABLE[IF NOT EXISTS][table_definition][IGNORE|REPLACE][AS]说明 该建表方式默认与...
维表JOIN语句
SELECT/*+SHUFFLE_HASH(D1)*/FROM src AS T LEFT JOIN dim1 FOR SYSTEM_TIME AS OF PROCTIME()AS D1 ON T.a=D1.a LEFT JOIN dim2 FOR SYSTEM_TIME AS OF PROCTIME()AS D2 ON T.b=D2.b-同时对维表dim1,dim2通过别名开启SHUFFLE_HASH联接。...
嵌套子查询
查询和分析语句*|SELECT diff[1]AS today,diff[2]AS yesterday,diff[3]AS ratio FROM(SELECT compare(PV,86400)AS diff FROM(SELECT count(*)AS PV FROM log))查询和分析结果 3337.0 表示当前1小时（例如2020-12-25 14:00:00~2020-12-25 15...
协同过滤etrec
create table etrec_test_input as select*from(select cast(0 as string)as user,cast(0 as string)as item from dual union all select cast(0 as string)as user,cast(1 as string)as item from dual union all select cast(1 as string...
查询数据
Q1 select l_returnflag,l_linestatus,sum(l_quantity)as sum_qty,sum(l_extendedprice)as sum_base_price,sum(l_extendedprice*(1-l_discount))as sum_disc_price,sum(l_extendedprice*(1-l_discount)*(1+l_tax))as sum_charge,avg(l_...
- 产品推荐
- 这些文档可能帮助您