TROCCO®でプロジェクト管理用の営業日データをBigQueryに整備する

はじめに

プロジェクトマネージャーとしてプロジェクトの統括をしているとき、自身やメンバーの作業工数をリアルタイムにモニタリングすることは非常に重要です。工数状況を把握することで、メンバーの支援やアサインの調整、リソースの調整、顧客との交渉など、プロジェクトの円滑な進行に向けた行動を取ることができます。

その際、予実ベースで進捗管理するのはまずやることですが、営業日のデータがあるとより理解を深めることができます。なぜなら、月によってそもそも営業日が少ないことがあったり、GWや年末年始などで営業日が偏っていることがあったりするため、当日時点の妥当な工数は営業日の状況によって変わってくるからです。

そこで、今回はTROCCO®を利用して、プロジェクト管理用の営業日データを整備する方法についてご紹介します。

取得データ

元データとしては、内閣府が公開している国民の祝日データを活用します。

（出典：国民の祝日について - 内閣府）

このページの中央部に、CSVファイルのリンク（https://www8.cao.go.jp/chosei/shukujitsu/syukujitsu.csv）が記載されています。過去の更新タイミングは不明ですが、2026年分は2025年2月に掲載されるとのことです。

（出典：同上）

データを確認してみると、1955年からの祝日のデータが、日付と名称が入った形で保持されています。

では、これをもとに営業日データを作成してみましょう。

TROCCO®の転送元HTTP・HTTPSでデータを自動取得する

転送設定として、転送元HTTP・HTTPS、転送先BigQueryを選択し、必要項目を記入していきます。

URLには先ほどのCSVファイルのURLを記入します。

文字エンコーディングをshift-jisに指定しますが、そのほか特別な設定は不要です。

BigQueryの接続情報を選択したあと、データセット、テーブル、ロケーションを選択し、転送モードを全件洗い替え（REPLACE）にしておきます。

自動データ設定を行い、下記のように設定していきます。

出力オプションで日付をDATE型で出力するように設定します。

これで設定は完了です。実行すると、BigQueryに次のようなテーブルが作成されます。

ところで、ここで「休日」というのが出てくるのが何かと思ったところ、祝日と祝日の間に挟まれた日が「国民の休日」となるからでした。

3.その前日及び翌日が「国民の祝日」である日（「国民の祝日」でない日に限る。）は、休日とする。

法律での名称が「休日」となっているので、正しいと言えば正しいのですが、やや分かりにくいですね・・・

TROCCO®のデータマート定義で営業日が分かるように加工する

クエリ実行モードとしてデータ転送モードを選択し、クエリ、出力先データセット、出力先テーブルを記入し、書き込みモードを全件洗い替えにします。

クエリの内容は下記の通りです。BigQueryではgenerate_date_arrayという連続する日付データを生成するための便利な関数があるので、それを利用して祝日だけだったデータから1日ごとの日付データを作り出しています。

-- データのある最初の年から最後の年までのデータを対象とする
with period as (
  select
    date_trunc(min(date_holiday), year) as date_start,
    date(extract(year from max(date_holiday)), 12, 31) as date_end
  from
    `project_id.utilities.japanese_holidays`
),

-- 国民の祝日ではないが組織で指定された休日を追加
additional_holidays as (
  select
    *
  from
    unnest([
      struct<month integer, day integer, holiday_name string>
      (12, 29, '年末年始休暇'),
      (12, 30, '年末年始休暇'),
      (12, 31, '年末年始休暇'),
      (1, 1, '年末年始休暇'),
      (1, 2, '年末年始休暇'),
      (1, 3, '年末年始休暇'),
      (1, 4, '年末年始休暇')
    ])
),

calendar as (
  select
    ymd,
    case extract(dayofweek from ymd)
      when 1 then 'Sun'
      when 2 then 'Mon'
      when 3 then 'Tue'
      when 4 then 'Wed'
      when 5 then 'Thu'
      when 6 then 'Fri'
      when 7 then 'Sat'
    end as day_of_week,
    case
      when coalesce(jh.holiday_name, ah.holiday_name) is not null then '祝日'
      when extract(dayofweek from ymd) in (1, 7) then '土日'
      else '平日'
    end as date_type,
    coalesce(jh.holiday_name, ah.holiday_name) as holiday_name
  from
    period p
  left join
    unnest(generate_date_array(date_start, date_end)) ymd
  left join
    `project_id.utilities.japanese_holidays` jh
    on
      ymd = jh.date_holiday
  left join
    additional_holidays ah
    on
      extract(month from ymd) = ah.month
      and extract(day from ymd) = ah.day
)

-- 月や年単位で何営業日目か、全体で何営業日あるかを算出
select
  ymd,
  day_of_week,
  date_type,
  holiday_name,
  case
    when date_type = '平日' then sum(case when date_type = '平日' then 1 end) over (partition by date_trunc(ymd, month) order by ymd rows between unbounded preceding and current row)
  end as weekdays_in_month,
  case
    when date_type = '平日' then sum(case when date_type = '平日' then 1 end) over (partition by date_trunc(ymd, month) order by ymd rows between unbounded preceding and unbounded following)
  end as total_weekdays_in_month,
  case
    when date_type = '平日' then sum(case when date_type = '平日' then 1 end) over (partition by date_trunc(ymd, year) order by ymd rows between unbounded preceding and current row)
  end as weekdays_in_year,
  case
    when date_type = '平日' then sum(case when date_type = '平日' then 1 end) over (partition by date_trunc(ymd, year) order by ymd rows between unbounded preceding and unbounded following)
  end as total_weekdays_in_year
from
  calendar

保存して実行すると、下記のような営業日データが作成されます。

元データの更新頻度が年次なので、手動実行でもそれほど負担ではないかもしれませんが、ワークフローにして毎年3月くらいに実行にしておくと、営業日データを自動で更新することもできます。

さいごに

設定としては簡単なものですが、営業日データは整備しておくと非常に便利です。よろしければやってみてくださいませ！