时间戳

首页 > 解决方案 > 在 SQL 中根据销售百分比创建客户集群

javascript - 在 SQL 中根据销售百分比创建客户集群

问题描述

我的数据集如下所示

数据

我想用以下逻辑创建最后一个列簇。需要始终在集群中提取前 3 个 ID。但是有条件，在选择前 3 个 ID 后，检查每个 ID 的个人 ID 百分比是否小于等于该组的 70。例如，对于ID =1, 检查 % 与对于,1000/(1000+500+400)类似。我们需要检查该组的个人 ID。在这里，对于所有 3 个 ID，我们都有 % ，因此我们将它们分组为 1。ID=2500/(1000+500+400)<=70%

现在，获取接下来的 3 组 ID 并执行与上述相同的过程。但是这里要注意的是，如果任何组 ID 的 % >70，那么我们需要在分组中选择下一个 ID 并遵循相同的过程，直到我们得到 % <=70。

例如，当我们第一次检查 % of 时ID=4，300/(300+50+70)它大于70%，因此我们在分组中添加下一个 ID，如果现在我们计算 % ID=4，它变为300/(300+50+70+40)小于70%。所以现在我们将ID=4,5,6,7 分组为集群 2。

我们需要遵循相同的过程，剩余的 ID 将转到 0 集群

标签： javascriptsql

解决方案

SQL Server 代码



--create table customers
    --( product varchar (200),
    --ids int,
    --sales numeric(18,4)
    --);


    --INSERT into customers values ('A',1,1000);
    --INSERT into customers values ('A',2,500);
    --INSERT into customers values ('A',3,400);
    --INSERT into customers values ('A',4,300);
    --INSERT into customers values ('A',5,70);
    --INSERT into customers values ('A',6,50);
    --INSERT into customers values ('A',7,40);
    --INSERT into customers values ('A',8,30);
    --INSERT into customers values ('A',9,30);
    --INSERT into customers values ('A',10,30);
    --INSERT into customers values ('A',11,20);

    SET NOCOUNT ON;

    DECLARE @Id INT=0,
            @cluster INT=1,
            @max_irow INT ,
            @max_cust_id INT 
        
    
    IF OBJECT_ID('tempdb..#Customers') IS NOT NULL
    DROP TABLE #Customers

    SELECT  ROW_NUMBER() OVER (ORDER BY ids) AS irow,
        Product,
        Ids,
        ISNULL(Sales,0.00) AS Sales,
        0 AS Cluster 
    INTO #Customers
    FROM customers WITH(NOLOCK)

    SELECT @max_cust_id=@@ROWCOUNT

    IF OBJECT_ID('tempdb..#tmp_Cluster') IS NOT NULL
    DROP TABLE #tmp_Cluster

    SELECT *,CAST(0.0000 AS NUMERIC) AS Percntg 
    INTO #tmp_Cluster 
    FROM #Customers WHERE 1=2

    WHILE(1=1)
    BEGIN

        INSERT INTO #tmp_Cluster(irow,Product,Ids,Sales,Cluster,Percntg )
        SELECT  irow,product,ids,sales,@cluster,sales/SUM(sales) OVER (PARTITION BY ISNULL(Cluster,1))*100
        FROM #Customers
        WHERE Cluster=0 AND irow<=@Id+3

        IF @@ROWCOUNT<3
        BREAK;
     
    
        WHILE(1=1)
        BEGIN
            IF EXISTS(SELECT 1 FROM #tmp_Cluster WHERE Percntg>=70.00)
            BEGIN
            
                SET @max_irow=NULL
                SELECT @max_irow=MAX(irow) FROM #tmp_Cluster

                TRUNCATE TABLE #tmp_Cluster

                INSERT INTO #tmp_Cluster(irow,Product,Ids,Sales,Cluster,Percntg)
                SELECT  irow,product,ids,sales,@cluster,sales/SUM(sales) OVER(PARTITION BY ISNULL(Cluster,1))*100.00
                FROM #Customers
                WHERE Cluster=0 AND irow<=@max_irow+1

            END
            IF NOT EXISTS(SELECT 1 FROM #tmp_Cluster WHERE Percntg>=70.00)
            BEGIN
        
                UPDATE A SET A.Cluster= @cluster
                FROM #Customers A
                INNER JOIN #tmp_Cluster B
                ON A.Product=B.Product
                AND A.Ids=B.Ids
                AND A.irow=B.irow
            END

                SELECT @Id=MAX(Irow) FROM #Customers WHERE Cluster<>0
                SELECT @cluster=MAX(Cluster)+1 FROM #Customers WHERE Cluster<>0

            BREAK;

        END     
 
        TRUNCATE TABLE #tmp_Cluster
    
    END

    SELECT Product,Ids,Sales,Cluster FROM #Customers

推荐阅读