javascript - 如何比较同一循环中的两个查询结果

Question

我有 2 个 sql 查询(每个查询一个学期)。这 2 个查询没有相同的行数。每个包含 3 个参数:Article id_Customer Turnover。

这是计算第一学期的请求:

select "LRU", "Client", round(sum("Montant_fac_eur"))
from "foundry_sync"."data"
where "Nature"='Repair'     

and extract(month from "Facturation") between 0+{{w_widget6.selectedValue}} and 5+{{w_widget6.selectedValue}}
group by "LRU", "Client"

查询的学期表示如下:

{
  "LRU": [
    "ATSU",
    "ATSU",
    "ATSU",
    "ATSU
      . 
      .
      .
   ],
"Client": [
    394,
    594,
    4001,
    5725,
    7057,
    7090
    .
    .
    .
],
 "round": [
    20866,
    16814,
    27421,
    0,
    6593,
    66965,
    8401
     . 
     .
     .
]

下面是结果示例:

   result 1                      result2

"[Article1,394,3914]"      "[Article1,394,3914]"
"[Article1,594,16814]"     "[Article2,594,10088]"
"[Article1,4001,26798]"    "[Article1,4001,11107]"
"[Article3,5725,0]"        "[Article3,5725,0]"
"[Article5,7057,5700]"     "[Article5,7057,3916]"
"[Article8,7090,56467]"    "[Article4,7090,17558]"
"[Article2,7236,8401]"     "[Article8,7236,8401]"
"[Article11,7242,12766]"   "[Article14,7242,4255]"
"[Article2,7262,892]"      "[Article2,7262,892]"
"[Article3,7302,17234]"    "[Article3,7302,8928]"

如您所见，一个客户可以在两个学期都有一篇文章的成交量，或者在一个学期(第一学期或第二学期)有一篇文章的成交量。我想首先比较每个学期每个 customer 的每个 Article 的营业额。

    var query1 = {{repair_semestre1}};
    var query2 = {{repair_semestre2}};
    var result1 = [];
    var result2 = [];


    for (var i = 0; i < query1.LRU.length; i++) {
      result1.push(formatName(query1.LRU[i], query1.Client[i], query1.round[i]));  
    }

    for (var i = 0; i < query2.LRU.length; i++) {
      result2.push(formatName(query2.LRU[i], query2.Client[i], query2.round[i]));  
    }

    return {
      result1: result1, 
      result2: result2
    };

   /*   for (var i = 0; i < query1.LRU.length, i < query2.LRU.length; i++) {
        if((query1.LRU[i] == query2.LRU[i]) && (query1.Client[i] == query2.Client[i])) {
                    if(query1.round[i] > query2.round[i]) {
                            return "ok";
                    }
                    else { return "non";
                    }
        }

    }  */

    function formatName(lru, turnover, round) {
      return "[" + lru + "," + turnover + "," + round + "]";
    }

我在函数外为这两个查询做了一个循环来比较她的结果:

var query1 = {{repair_semestre1}};
var query2 = {{repair_semestre2}};
for (var i = 0; i < query1.LRU.length, i < query2.LRU.length; i++) {
    if((query1.LRU[i] == query2.LRU[i]) && (query1.Client[i] == query2.Client[i])) {
                if(query1.round[i] > query2.round[i]) {
                        return "ok";
                }
                else { return "not ok";
                }
    }   
}  

它只返回not ok，并且不完成其余的行。有人可以解释一下为什么吗？以及如何在我的函数中添加这个循环来运行？

谢谢。

Answer 1

我建议首先将您的数据结构转换为将相关事物组合在一个对象中的东西，而不是与索引号链接。

例如，对于涉及两篇文章的客户 594，学期周转率可以表示为:

{
  "594": {
    "Article1": [
      16814,
      0
    ],
    "Article2": [
      0,
      10088
    ]
  }
}

... 其中内部数组始终有两个条目:两个学期中的每一个。该结构将允许轻松报告。

下面是转换代码及其后的示例输出:

// Sample data
const query1 = {
    LRU: ["Article1", "Article1", "Article1", "Article3", "Article5", 
          "Article8", "Article2", "Article11", "Article2", "Article3"], 
    Client: [394, 594, 4001, 5725, 7057, 7090, 7236, 7242, 7262, 7302],
    round: [3914, 16814, 26798, 0, 5700, 56467, 8401, 12766, 892, 17234]
}, query2 = {
    LRU: ["Article1", "Article2", "Article1", "Article3", "Article5", 
          "Article4", "Article8", "Article14", "Article2", "Article3"], 
    Client: [394, 594, 4001, 5725, 7057, 7090, 7236, 7242, 7262, 7302],
    round: [3914, 10088, 11107, 0, 3916, 17558, 8401, 4255, 892, 8928]
};

// Convert to a more useful data structure, keyed by client and article
const data = {};
[query1, query2].forEach(function (query, semester) {
    query.Client.forEach(function(clientId, index) {
        var client = data[clientId] = data[clientId] || {};
        var clientArt = client[query.LRU[index]] = client[query.LRU[index]] || [0, 0];
        clientArt[semester] = query.round[index];
    });
});

// Now report on that data
for (const client in data) {
    for (const article in data[client]) {
        const turnovers = data[client][article];
        console.log('Client: ' + client + ', art.: ' + article 
                  + ', semester t/o: ' + turnovers
                  + ' ' + (turnovers[0] === turnovers[1] ? 'equal' : 
                          turnovers[0] < turnovers[1] ? 'increase' : 
                                                        'decrease'));
    }
}

.as-console-wrapper { max-height: 100% !important; top: 0; }