任意商品组合【连带计算】

需求是要计算商品的连带情况,即商品组合。比如一个订单同时买了ABC3个商品,则有A、B、C、AB、AC、BC、ABC7种组合情况,单个不算为4种。
一条数据记录里的商品组合就需要循环计算,当数据量大时性能是问题。
尝试了很多方法都无法适应大数据量,目前想到1个还算“将就”的解法。

  1. 一张组合表
    几个数的组合情况,当个数已定,其组合情况即确定。
    当真实数据代入后,取本表的idx作为数组下标。
create table dim_combination(id int,idx int[],counts int)
;
insert into dim_combination values(1,array[1],1)
;
-- 依次插入从1开始直到10个数的组合
insert into dim_combination
with t as(
select 10 as id
)select t.id,b.idx||t.id,t.counts+1
from t left join tenant_1800000143_rst.dim_combination b on t.id=b.id+1 
union all 
select t.id,b.idx,t.counts
from t left join tenant_1800000143_rst.dim_combination b on t.id=b.id+1
union all 
select t.id,array[t.id],1 from t

数据预览

id idx counts
1 {1} 1
2 {1} 1
2 {2} 1
2 {1,2} 2
3 {3} 1
3 {1,2,3} 3
3 {2,3} 2
3 {1,3} 2
3 {1} 1
3 {2} 1
3 {1,2} 2
  1. 一个转换函数
    传入数组和组合下标,返回该数组的所有组合。
    (这步不佳,用到了循环。本打算用动态sql,将下标1,2,3替换成arr[1],arr[2],arr[3]进行动态sql查询返回,一直出错,后续再优化。)
CREATE OR REPLACE FUNCTION f_get_combination(arr text[],arr_id int[])
RETURNS text[] as
-- select f_get_combination(array['a','b','c'],array[1,2])
$BODY$
declare sql text;
    rst text[];
i int;
begin
for i in 1..array_length(arr_id,1) loop
rst=rst||arr[i];
end loop
;
return rst;
end
$BODY$
LANGUAGE plpgsql
  1. 查询示范regexp_split_to_array(skc_codes,',')
select a.* ,b.idx,f_get_combination(skc_codes,idx)
from bi_fct_sales_skcs a
inner join dim_combination b on a.counts=b.id and b.counts>1

如下面3个商品,得到4种组合情况。

skc_codes counts idx f_get_combination
KC19324590046,KM19419997039,KN19416516051 3 {1,2} {KC19324590046,KM19419997039}
KC19324590046,KM19419997039,KN19416516051 3 {1,3} {KC19324590046,KM19419997039}
KC19324590046,KM19419997039,KN19416516051 3 {2,3} {KC19324590046,KM19419997039}
KC19324590046,KM19419997039,KN19416516051 3 {1,2,3} {KC19324590046,KM19419997039,KN19416516051}

运用

-- 构造各个订单的skc组合(去重+排序)(不含1个及10个以上的)
create table tenant_1800000143_rst.fct_sales_skcs as
select doc_id,array_agg(distinct skc_code order by skc_code) skc_codes,count(distinct skc_code) counts
--select * -- select count(*)--732123
from tenant_1800000143_rst.bi_fct_skc_sales_detail
group by doc_id 
having count(distinct skc_code)>1 and count(distinct skc_code)<10
;
-- 生成skc的任意个数组合
create table tenant_1800000143_rst.fct_sales_skcs_combination as
select a.* ,b.idx,f_get_combination(skc_codes,idx)
from tenant_1800000143_rst.fct_sales_skcs a
inner join dim_combination b on a.counts=b.id and b.counts>1

动态sql生成组合(函数改写)

CREATE OR REPLACE FUNCTION public.f_get_combination2(arr text[], arr_id text)
 RETURNS text[] 
 -- select public.f_get_combination2(array['a','b','c'],'1,3')
 LANGUAGE plpgsql
AS $function$
declare sql text;
    rst text;--[];
i int;
begin
    sql='select array[$1['||replace(arr_id,',','],$1[')||']]';
    raise notice 'sql: %',sql; 
execute sql using $1 into rst;
--execute 'select count(*) from dictionarys where parentid=$1' using $1 into v_count;
return rst;
end
$function$
;
最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 203,456评论 5 477
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 85,370评论 2 381
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 150,337评论 0 337
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 54,583评论 1 273
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 63,596评论 5 365
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 48,572评论 1 281
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 37,936评论 3 395
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 36,595评论 0 258
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 40,850评论 1 297
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,601评论 2 321
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,685评论 1 329
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 33,371评论 4 318
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 38,951评论 3 307
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,934评论 0 19
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,167评论 1 259
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 43,636评论 2 349
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 42,411评论 2 342

推荐阅读更多精彩内容