如何计算每学期的营业额

Question

我有一个column在SQL table。其中包含一个date交货单。

因此，可以重复相同的日期（在一天中，我们会下达几笔订单），如下所示：

05-01-16
05-01-16
05-01-16
08-01-16
08-01-16
14-01-16
22-01-16
22-01-16
04-02-16
05-02-16
05-02-16

我要计算的每篇文章的营业额的AVG each 6 months，我将进一步说明：

From January to June ==> Turnover 1
From Febrary to July ==> Turnover 2
From March to August ==> Turnover 3
From April to September ==> Turnover 4
From May to Obtober ==> Turnover 5
From June to November ==> Turnover 6
From July to December ==> Turnover 7

我已经在下面的请求中提取了月份，但是我无法动态计算（因为我的数据应该每月更改）上面的示例所示的营业额：

select distinct extract (month from Article) as mt 
order by mt

我尝试使用，cursor但无法获得最佳解决方案。

我提出了一个要求，以计算以下内容the first 6 months（我手动完成）中每位客户的营业额：

select "LRU", "Client", round(sum("Montant_fac_eur"))
from "foundry"
where "Nature"='Repair' 
and "Client"={{w_widget3.selectedValue}}
and "annee"='2016'
and extract (month from "date") between '1' and '6'


group by "LRU", "Client"

她的结果如下：

 LRU            Client  round
"article1"       4001   8859     Turnover of article1 from January to June
"article2"       4001   94315    Turnover of article2 from January to June
"article3"       4001   273487   Turnover of article3 from January to June
"article4"       4001   22292    Turnover of article4 from January to June
"article5"       4001   22292    Turnover of article5 from January to June
"article6"       4001   42590    Turnover of article6 from January to June
"article7"       4001   9965     Turnover of article7 from January to June
"article8"       4001   39654    Turnover of article8 from January to June
"article9"       4001   3883     Turnover of article9 from January to June
"article10"      4001   41612    Turnover of article10 from January to June

我想做一个循环来计算每6个月的营业额，而如果可能的话不手动编写？有人可以帮我，给我一个解决方案或建议，我该怎么办？谢谢你。

Answer 1

在这里，您可以查看问题的简化定义和解决方案（如果我理解正确的话）：http :
//sqlfiddle.com/#!9/48a2e1/1

CREATE TABLE foundry
(
    lru varchar(50) NOT NULL,
    client int  NOT NULL,
    purchase_date date,
    price int NOT NULL
);

INSERT INTO foundry (lru, client, purchase_date, price) VALUES
("article1", 4001, "01-01-16", 100),
("article1", 4001, "01-01-17", 200),
("article1", 4001, "01-02-16", 300),
("article1", 4001, "01-04-16", 400),
("article1", 4001, "01-06-16", 500),
("article1", 4001, "01-08-16", 600),
("article1", 4001, "01-10-16", 700),
("article1", 4001, "01-11-16", 800),
("article1", 4002, "01-01-16", 900),
("article1", 4002, "01-07-16", 1000),
("article1", 4002, "01-12-16", 1100);

基本上，我们有一个包含四列的表：lru（商品名称），客户，购买日期和一些价格。

解决方案如下所示：

SELECT lru, client, avg(price), COUNT(*) as total_items,
MONTHNAME(STR_TO_DATE(L, '%m')) as start_month, MONTHNAME(STR_TO_DATE(R, '%m')) as end_month FROM foundry,
(
  SELECT 1 as L, 6 as R
    UNION ALL
  SELECT 2, 7
    UNION ALL
  SELECT 3, 8
    UNION ALL
  SELECT 4, 9
    UNION ALL
  SELECT 5, 10
    UNION ALL
  SELECT 6, 11
    UNION ALL
  SELECT 7, 12
) months
WHERE month(purchase_date) >= L AND month(purchase_date) <= R
GROUP BY lru, client, L, R

这个想法是：

1. 生成月份的所有可能组合：1-6、2-7，…，7.12
2. 用生成的月份组合合并源数据
3. 将AVG与GROUP BY一起使用

结果是：

lru     client  avg(price)  total_items     start_month     end_month
article1    4001    300     5   January     June
article1    4001    400     3   February    July
article1    4001    500     3   March   August
article1    4001    500     3   April   September
article1    4001    600     3   May     October
article1    4001    650     4   June    November
article1    4001    700     3   July    December
article1    4002    900     1   January     June
article1    4002    1000    1   February    July
article1    4002    1000    1   March   August
article1    4002    1000    1   April   September
article1    4002    1000    1   May     October
article1    4002    1000    1   June    November
article1    4002    1050    2   July    December