如何计算每学期的营业额 [英] How to compute the turnover each semester

问题描述

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

I have a column in an SQL table. That contains a dateof delivery order.

因此可以重复相同的日期(有一天我们会下达几笔订单)，就像这样:

So the same date can be repeat (in one day we delivred severals orders), like this:

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

我想计算each 6 months中每篇文章的营业额的平均值，我将解释更多:

I want, compute the AVG of the turnover of each article in each 6 months, I explain more:

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

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

I'm already extracted the month by the request bellow, but I can't compute dynamically (because my data should be change each month) the turnover like this example above:

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

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

I tried to use a cursor but I can't arrived to the best solution.

我请求计算the first 6 months中每篇文章的每位客户的营业额(我是手动完成的)，如下所示:

I did a request to compute a turnover for each customer per article in the first 6 months (I did it manually ) is the following:

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个月的营业额，如果可能的话不手动编写? 有人可以帮我，给我一个解决方案或建议，我该怎么办? 谢谢.

I want do a loop to compute a turnover each 6 months without to write it manually if it possible ? Can someones please help me and give me a solution or suggestion how can I do it ? Thank you.

推荐答案

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

Here you can view the simplified definition and solution of your problem (if I understood you correctly): 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(商品名称)，客户，购买日期和一些价格.

Basically we have a table with four columns: lru (article name), client, purchase date, and some price.

解决方案如下:

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

这篇关于如何计算每学期的营业额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！