53127


お世話になります。

業務で、今度の四月から開始される『働き方改革』の中に、複数月の平均が80時間を超えてはならないと  
いうのがあり、それを求めるためのツールをAccessで作成しています。

四月から始まり、三月で終わる一年の期間の中で、二ヶ月以上の連続した組み合わせ(4-5,4-5-6,4-5-6-7～1-2-3,2-3まで)の平均を算出するのですが、数を数えてみると合計で66パターンの組み合わせができてしまいます。これを人数分だけ計算するとなると、かなりの時間がかかると思います。  
勿論、一つ一つ行って、一度でも80時間超えがあれば、そこで確認済みにすれば良い話ではあるのですが、これをもっと効率的にやりたいと思っています。

上記のような、『総当たりの平均』を求めるやり方で、もっと効率の良い方法はありますでしょうか？SQLなどでできれば、とてもエレガントだとは思いますが…。SQLの平均の記述が解らないのではなく、コードのようにループさせなくて済む方法です。

強引にコードに直すと、下記のようになると思います。

[code]

    　　　Dim arr(11) As Long　'　ここでは宣言しかしていませんが、既に中身があるものと思ってください。
    　　　Dim d0 As Long, d1 As Long, d2 As Long
        Dim val As Double
        Dim flg As Boolean
    
        flg = False
        For d0 = 0 To 11 - 1
            For d1 = d0 + 1 To 12 - 1
                val = 0#
                For d2 = d0 To d1
                    val = val + arr(d2)
                    If (val / ((d1 - d0) + 1) > 80) Then
                        flg = True
                        GoTo End_for
                    End If
                Next
            Next
        Next
    
    End_for:
    
[/code]

よろしくお願いいたします。


CC BY-SA 4.0
2019-03-02T01:09:07.277
53123
2019-03-11T06:51:53.987
2019-03-02T02:44:03.977
9374
9374
question
sql アルゴリズム
12ヶ月の中の複数月の平均を求めるアルゴリズム


(面白そうなんで考えてみました。回答として妥当では無いと思うのですが、コメント欄には書ききれなそうだったので…他の方がこんな問題に対してどういうふうに考えられるのか興味あります。)

* * *

  * 連続する2ヶ月の平均が80時間超ならば、少なくともどちらかが80時間超 
    * (裏)連続する2ヶ月の平均が80時間以下ならば、少なくともどちらかが80時間以下

と

  * 連続する3ヶ月(m1,m2,m3)平均が80時間超で、かつ、その期間に含まれる連続する2ヶ月平均(※組み合わせは(m1,m2),(m2,m3)の2パターン)が全て80時間以下なら、端の月(期間の開始月m1、終了月m3、の2月)のいずれも80時間超

は正しそうで、ここから

  * 連続するnヶ月平均が80時間超で、かつ、その期間に含まれる連続するn-1ヶ月平均が全て(※組み合わせは2パターン)80時間以下なら、端の月のいずれも80時間超

も言えそうな気がするので

  1. 80時間超の月と前月との2ヶ月平均
  2. 80時間超の月と翌月との2ヶ月平均
  3. 80時間超の月と、翌直近の80時間超の月との間の平均

を調べれば良さそうに思われます。

単月での80時間超は許容される、というのが3.以外に1.,2.も調べないといけないのに繋がっているのかな、と。


CC BY-SA 4.0
2019-03-02T05:45:54.090
53125
2019-03-02T05:56:09.900
2019-03-02T05:56:09.900
2808
2808
53123
answer


『働き方改革』については、[時間外労働の上限規制の「2~6ヵ月平均で80時間以内」とは？【労働基準法改正
2019】](http://ttps://www.ieyasu.co/media/max_80h-average/) の記事を参考にしました。

この記事によると、「複数月の平均が80時間を超えてはならない」というのは正確には「2ヵ月ないし6か月平均で80時間以内」ということで、  
その意味は、“2ヵ月、3ヵ月、4ヵ月、5ヵ月、6か月のいずれの期間においても、月平均80時間以内にしなければならない”。

そうすると、平均を計算しないといけないパターンは、以下の４５種類  
２か月：１１パターン（４月から、５月から、・・・、２月から）  
３か月：１０パターン（４月から、５月から、・・・、１月から）  
４か月：９パターン（４月から、５月から、・・・、１２月から）  
５か月：８パターン（４月から、５月から、・・・、１１月から）  
６か月：７パターン（４月から、５月から、・・・、１０月から）

月の時間外労働時間が80時間を超えているかどうかの判断にも計算時間がかかるので、80時間を超えるケースが見つかるまで、全てのパターンを順に計算してみる以下のような愚直なコードのほうが計算時間は短くなると思います。

[code]

    Dim overtime(12) As Double　'　12か月(４月から３月まで）の時間外労働時間の配列（データは事前に入れられていると想定)
    Dim numOfMonth as Integer, startMonth as Integer, ov as Integer
    Dim val As Double
    Dim flg As Boolean
    
    flg = False
    For numOfMonth = 2 To 6  '平均をとる月の数を、２から６まで変えて調べる
        For startMonth = 0 to 12-numOfMonth '
            val = 0#
            For ov = startMonth To startMonth + numOfMonth -1
                val = val + overtime(ov)
            Next ov
            If ((val / numOfMonth) > 80) Then '月平均時間外労働時間が80時間超えか？
                flg = True
                GoTo End_for
            End If
        Next startMonth
    Next numOfMonth
    
    End_for:
      ' flagの値に基づいて、結果を表示する
    
[/code]


CC BY-SA 4.0
2019-03-02T06:29:33.707
53127
2019-03-02T06:29:33.707
217
53123
answer


Fumu 7さんの回答ですが

> そうすると、平均を計算しないといけないパターンは、以下の４５種類  
>  ２か月：１１パターン（４月から、５月から、・・・、２月から）  
>  ３か月：１０パターン（４月から、５月から、・・・、１月から）  
>  ４か月：９パターン（４月から、５月から、・・・、１２月から）  
>  ５か月：８パターン（４月から、５月から、・・・、１１月から）  
>  ６か月：７パターン（４月から、５月から、・・・、１０月から）

もう少し簡略化できます。

４か月パターンですが１例を挙げると4-7で平均80時間を超える場合、平均の原理から4-5と6-7のどちらかが80時間を超えることになります。4-5も6-7も２か月パターンで検査済みですから、４か月パターンをチェックする必要はありません。同様に５か月、６か月も省略可能です。

  * ２か月： 4-5, 5-6, 6-7, 7-8, 8-9, 9-10, 10-11, 11-12, 12-1, 1-2, 2-3
  * ３か月： 4-6, 5-7, 6-8, 7-9, 8-10, 9-11, 10-12, 11-1, 12-2, 1-3

の計２１パターンの検査で済みます。  
考え方次第ですが、ここまで絞り込めたのであれば、もう愚直にハードコーディングされるのも１つの手です。

* * *

yukihaneさんの考え方はここから更に数学的に候補を減らしたものです。ただし「80時間超の月」を探す処理が必要になっていますのでSQLでの記述は難しくなります。


CC BY-SA 4.0
2019-03-04T08:22:58.377
53190
2019-03-04T08:22:58.377
4236
53123
answer


もし、 CTE が使える DB につながっているのであれば、以下のようにすると、 66 パターンを sql 上で一発で取得できたりします。(下記は、
mysql の場合)

[code]

    -- base は、適宜、 DB からの月次の集計結果クエリに置き換える
    with recursive base as (
      select 4 as `month`, 74 as hours
      union all
      select 5, 75
      union all
      select 6, 76
      union all
      select 7, 77
      union all
      select 8, 78
      union all
      select 9, 79
      union all
      select 10, 80
      union all
      select 11, 81
      union all
      select 12, 82
      union all
      select 1, 83
      union all
      select 2, 84
      union all
      select 3, 85
    ), unioned as (
        select CAST(`month` as CHAR(255)) as months, `month` as last_month, 1 as num_months, hours * 1.0 as hours from base
      union all
        select concat(months, '-', `month`), `month` as last_month, num_months + 1, (unioned.hours * num_months + base.hours) * 1.0 / (num_months + 1)
        from unioned inner join base on (unioned.last_month) % 12 + 1 = base.`month`
        where unioned.last_month <> 3
    )
    select * from unioned
    where num_months > 1;
    
[/code]

実行結果

[code]

    +----------------------------+------------+------------+-------+
    | months                     | last_month | num_months | hours |
    +----------------------------+------------+------------+-------+
    | 4-5                        |          5 |          2 |  74.5 |
    | 5-6                        |          6 |          2 |  75.5 |
    | 6-7                        |          7 |          2 |  76.5 |
    | 7-8                        |          8 |          2 |  77.5 |
    | 8-9                        |          9 |          2 |  78.5 |
    | 9-10                       |         10 |          2 |  79.5 |
    | 10-11                      |         11 |          2 |  80.5 |
    | 11-12                      |         12 |          2 |  81.5 |
    | 12-1                       |          1 |          2 |  82.5 |
    | 1-2                        |          2 |          2 |  83.5 |
    | 2-3                        |          3 |          2 |  84.5 |
    | 4-5-6                      |          6 |          3 |  75.0 |
    | 5-6-7                      |          7 |          3 |  76.0 |
    | 6-7-8                      |          8 |          3 |  77.0 |
    | 7-8-9                      |          9 |          3 |  78.0 |
    | 8-9-10                     |         10 |          3 |  79.0 |
    | 9-10-11                    |         11 |          3 |  80.0 |
    | 10-11-12                   |         12 |          3 |  81.0 |
    | 11-12-1                    |          1 |          3 |  82.0 |
    | 12-1-2                     |          2 |          3 |  83.0 |
    | 1-2-3                      |          3 |          3 |  84.0 |
    | 4-5-6-7                    |          7 |          4 |  75.5 |
    | 5-6-7-8                    |          8 |          4 |  76.5 |
    | 6-7-8-9                    |          9 |          4 |  77.5 |
    | 7-8-9-10                   |         10 |          4 |  78.5 |
    | 8-9-10-11                  |         11 |          4 |  79.5 |
    | 9-10-11-12                 |         12 |          4 |  80.5 |
    | 10-11-12-1                 |          1 |          4 |  81.5 |
    | 11-12-1-2                  |          2 |          4 |  82.5 |
    | 12-1-2-3                   |          3 |          4 |  83.5 |
    | 4-5-6-7-8                  |          8 |          5 |  76.0 |
    | 5-6-7-8-9                  |          9 |          5 |  77.0 |
    | 6-7-8-9-10                 |         10 |          5 |  78.0 |
    | 7-8-9-10-11                |         11 |          5 |  79.0 |
    | 8-9-10-11-12               |         12 |          5 |  80.0 |
    | 9-10-11-12-1               |          1 |          5 |  81.0 |
    | 10-11-12-1-2               |          2 |          5 |  82.0 |
    | 11-12-1-2-3                |          3 |          5 |  83.0 |
    | 4-5-6-7-8-9                |          9 |          6 |  76.5 |
    | 5-6-7-8-9-10               |         10 |          6 |  77.5 |
    | 6-7-8-9-10-11              |         11 |          6 |  78.5 |
    | 7-8-9-10-11-12             |         12 |          6 |  79.5 |
    | 8-9-10-11-12-1             |          1 |          6 |  80.5 |
    | 9-10-11-12-1-2             |          2 |          6 |  81.5 |
    | 10-11-12-1-2-3             |          3 |          6 |  82.5 |
    | 4-5-6-7-8-9-10             |         10 |          7 |  77.0 |
    | 5-6-7-8-9-10-11            |         11 |          7 |  78.0 |
    | 6-7-8-9-10-11-12           |         12 |          7 |  79.0 |
    | 7-8-9-10-11-12-1           |          1 |          7 |  80.0 |
    | 8-9-10-11-12-1-2           |          2 |          7 |  81.0 |
    | 9-10-11-12-1-2-3           |          3 |          7 |  82.0 |
    | 4-5-6-7-8-9-10-11          |         11 |          8 |  77.5 |
    | 5-6-7-8-9-10-11-12         |         12 |          8 |  78.5 |
    | 6-7-8-9-10-11-12-1         |          1 |          8 |  79.5 |
    | 7-8-9-10-11-12-1-2         |          2 |          8 |  80.5 |
    | 8-9-10-11-12-1-2-3         |          3 |          8 |  81.5 |
    | 4-5-6-7-8-9-10-11-12       |         12 |          9 |  78.0 |
    | 5-6-7-8-9-10-11-12-1       |          1 |          9 |  79.0 |
    | 6-7-8-9-10-11-12-1-2       |          2 |          9 |  80.0 |
    | 7-8-9-10-11-12-1-2-3       |          3 |          9 |  81.0 |
    | 4-5-6-7-8-9-10-11-12-1     |          1 |         10 |  78.5 |
    | 5-6-7-8-9-10-11-12-1-2     |          2 |         10 |  79.5 |
    | 6-7-8-9-10-11-12-1-2-3     |          3 |         10 |  80.5 |
    | 4-5-6-7-8-9-10-11-12-1-2   |          2 |         11 |  79.0 |
    | 5-6-7-8-9-10-11-12-1-2-3   |          3 |         11 |  80.0 |
    | 4-5-6-7-8-9-10-11-12-1-2-3 |          3 |         12 |  79.5 |
    +----------------------------+------------+------------+-------+
    66 rows in set (0.00 sec)
    
[/code]


CC BY-SA 4.0
2019-03-04T11:29:21.427
53193
2019-03-04T11:44:35.313
2019-03-04T11:44:35.313
754
754
53123
answer


SQLで総当たりを求めると考えると  
再帰CTEの使えないACCESSでやるのであれば、素直に66通りの組み合わせテーブルを作るのが良いんじゃないでしょうか

仮に組み合わせテーブルを  
FromTo(from Integer,to integer)として  
(1,2)  
(1,3)  
...  
(1,12)  
(2,3)  
...  
(2,12)  
(11,12)  
の66レコードを用意し  
あとは

[code]

    SELECT 
    FromTo.from, 
    FromTo.to, 
    (select avg(時間) from クエリ1 where 月番号>= FromTo.from and 月番号<=FromTo.to) AS 平均時間
    FROM 
    FromTo;
    
[/code]

のようなクエリで取れます

月番号は年度ごとに4月～翌3月を1～12になるように調整している前提です  
例えばこんなクエリ

[code]

    SELECT 
    IIf(Month(年月)<4,Year(年月)-1,Year(年月)) AS 年度, 
    IIf(Month(年月)<4,Month(年月)+9,Month(年月)-3) AS 月番号, 
    テーブル1.時間
    FROM 
    テーブル1
    WHERE 
    (((IIf(Month(年月)<4,Year(年月)-1,Year(年月)))=2018));
    
[/code]


CC BY-SA 4.0
2019-03-06T14:54:37.617
53249
2019-03-06T14:54:37.617
9811
53123
answer


  * パターンが有限
  * 年度単位で、人別に残業時間を集計する必要がありそう

という点を踏まえると、各パターンを計算するSQLを愚直に書き、結果をUNIONで結合するのが簡単かな、思いました。

以下のような感じを考えています。

[code]

    -- テスト用の仮テーブル
    CREATE TABLE test (
        name varchar 
        ,year  int
        ,month int
        ,hour float);
    
    -- テスト用データの準備    
    INSERT INTO test VALUES ('A', 2019, 4,  60.0)
    INSERT INTO test VALUES ('A', 2019, 5,  30.0)
    INSERT INTO test VALUES ('A', 2019, 6,  20.0)
    INSERT INTO test VALUES ('A', 2019, 7,  40.0)
    INSERT INTO test VALUES ('A', 2019, 8,  80.0)
    INSERT INTO test VALUES ('A', 2019, 9,  70.4)
    INSERT INTO test VALUES ('A', 2019, 10, 100.4)
    INSERT INTO test VALUES ('A', 2019, 11, 20.4)
    INSERT INTO test VALUES ('A', 2019, 12, 30.4)
    INSERT INTO test VALUES ('A', 2019, 1,  30.4)
    INSERT INTO test VALUES ('A', 2019, 2,  10.4)
    INSERT INTO test VALUES ('A', 2019, 3,  20.4)
    INSERT INTO test VALUES ('B', 2019, 4,  10.4)
    INSERT INTO test VALUES ('B', 2019, 5,  30.4)
    INSERT INTO test VALUES ('B', 2019, 6,  60.4)
    INSERT INTO test VALUES ('B', 2019, 7,  10.4)
    INSERT INTO test VALUES ('B', 2019, 8,  30.4)
    INSERT INTO test VALUES ('B', 2019, 9,  60.4)
    INSERT INTO test VALUES ('B', 2019, 10, 10.4)
    INSERT INTO test VALUES ('B', 2019, 11, 30.4)
    INSERT INTO test VALUES ('B', 2019, 12, 60.4)
    INSERT INTO test VALUES ('B', 2019, 1,  10.4)
    INSERT INTO test VALUES ('B', 2019, 2,  30.4)
    INSERT INTO test VALUES ('B', 2019, 3,  60.4)
    
    -- パターン別に計算してUNION
    SELECT name, year, '4-5' AS 'target', avg(hour) AS 'avg_hour' FROM test WHERE month In (4, 5) GROUP BY year, name
    union
    SELECT name, year, '5-6' AS 'target', avg(hour) AS 'avg_hour' FROM test WHERE month In (5, 6) GROUP BY year, name
    union
    (以下略)
    
    
[/code]

80時間超過を抽出したければ、`HAVING avg(hour) > 80`を 各パターンのクエリの最後に加える手もあります。


CC BY-SA 4.0
2019-03-10T16:19:04.483
53348
2019-03-10T16:19:04.483
12561
53123
answer


皆さん、ありがとうございます。  
皆さんのヒントを元に、自分なりに考えてみた『自己レス』です。

もっと良い方法があると思いますので、指摘などしていただければ幸いです。

[![画像の説明をここに入力](https://i.stack.imgur.com/3ekLn.jpg)](https://i.stack.imgur.com/3ekLn.jpg)

こんなテーブルを作成しました。m0からm11には、四月から始まり、翌年三月までの  
残業時間が入っている者とします。

その結果、下記のコードを実行しました。

[code]

    Private Sub calcTotalAverage()
        Dim d0 As Long, d1 As Long, d2 As Long
        Dim condition As String
        Dim dtSt As Date
    
        dtSt = #2018/04/01#
    
       '   6か月間総当たり
        For d0 = 0 To 11 - 1
            For d1 = d0 + 1 To 12 - 1
    
    　　　　　　　　　'　最長六ヶ月のため
                If ((d1 - d0) > 6) Then
                    Exit For
                End If
    
                SQL = "UPDATE T_overtime SET [最大平均時間] = "
    
                condition = "("
                For d2 = d0 To d1
                    condition = condition & "m" & d2 & " + "
                Next
                condition = condition & "0) / " & (d1 - d0 + 1)
                SQL = SQL & condition & ",[最大平均開始月] = '" & Format(DateAdd("m", d0, dtSt), "m月") & "',[最大平均終了月] = '" & Format(DateAdd("m", d1, dtSt), "m月") & "' WHERE [最大平均時間] < " & condition
    
                CurrentDb.Execute (SQL)
            Next
        Next
    
    End Sub
    
[/code]

最終的に、平均の最大値が80を超えているかどうかを見るので、ひとまず愚直に全員の最大の平均時間を算出しました。その上で、『最大平均時間』が80を超えているかどうかを見れば良いかと。

ここまでは単純に総当たりですが、皆様から教えていただいた『素数の平均』の考え方が入れば、もっと  
効率よくなるのではないかと思うのですが、いかがでしょうか。

よろしくお願いいたします。


CC BY-SA 4.0
2019-03-11T06:35:03.607
53358
2019-03-11T06:51:53.987
2019-03-11T06:51:53.987
9374
9374
53123
answer