上述回答遗漏了大月有31天的情况,第三十一天应该属于下旬,正确的代码应为:
select avg(price), sum(qty) from t1 group by iif(dayOfMonth(date)>30,date-10,date-(dayOfMonth(date) - 1) % 10) as xun
请教一下,semiMonthBegin(X, [dayOfMonth=15], [offset], [n=1])函数的逻辑总感觉哪里不对,对于以默认的dayOfMonth=15天周期来说运行的很好,但是如果我想以旬为时间单位进行统计,设置dayOfMonth=10,结果就不对了,是否有什么办法可以方便的按旬进行统计呢?
select avg(price),sum(qty) from t1 group by semiMonthBegin(date,10);
上述回答遗漏了大月有31天的情况,第三十一天应该属于下旬,正确的代码应为:
select avg(price), sum(qty) from t1 group by iif(dayOfMonth(date)>30,date-10,date-(dayOfMonth(date) - 1) % 10) as xun
select avg(price), sum(qty) from t1 group by date - (dayOfMonth(date) - 1) % 10 as xun
你好 按旬统计的话semiMonthBegin这个函数不适合,这个函数请参考 https://www.dolphindb.cn/cn/help/200/FunctionsandCommands/FunctionReferences/s/semiMonthBegin.html?highlight=semimonthbegin
要实现按旬统计,可以自定义函数增加一列,再group by统计,代码如下
def f(x){
Count =x.size()
res = array(STRING, Count)
for(i in 0:Count){
if(int(x[i].format("dd"))<=10){
res[i]=x[i].format("yyyy.MM.")+"10"
}
else if (int(x[i].format("dd"))<=20)
{
res[i]=x[i].format("yyyy.MM.")+"20"
}
else{
res[i]=monthEnd(x[i]).format("yyyy.MM.dd")
}
}
return res
}
t1=select *, date(f(date)) as xun from t1
select avg(price),sum(qty) from t1 group by xun;
也可以用dayOfMonth函数,可以按照任意天数来统计
select *,date-(dayOfMonth(date) - 1) % 10 as xun from t1