Youtubeビッグデータ解析 その4 課金者ランキング

ホロライブメンバーのチャンネルテーブルを作って、

一定期間内の動画一覧を作って、

SELECT
    ch.author_name
    ,v.id
    ,v.name
    ,v.uploaded_date
FROM channels ch
    INNER JOIN videos v ON ch.id = v.channel_id
WHERE
    v.uploaded_date BETWEEN '2020-07-13 00:00:00' AND '2020-07-19 23:59:59'
ORDER BY
    ch.id
    ,v.uploaded_date DESC

対象動画のチャット情報を全て取得してみた。

これで、
takikomiprogramming.hateblo.jp
の処理をかければ
動画のランキングを作成する事が可能になった。
1動画からのチャット情報取得に平均4分。今回の集計対象動画数が146本なので、10時間程度で取得できる計算になる。
今回はお試しなので、一気に取得はやってない。
まぁ、情報取得もランキング作成も十分自動化できそう。
チャット情報取得は高速化する案はいくらでもあるわけだが、Yotube側に負荷をかける行為になるので、止めておこう。

で、ランキング表を作る前にもうちょっとやりたい事がある。

課金者ランキングの作成

期間区切って、アカウントごとに課金額合計を取るとこうなる。
※名前やらYotubeアカウント情報も出せるわけだが、有識者から「炎上するから止めろ。」と言われたので、アイコンのみとする。
※アイコンも表示しない用に変更しました。

順位	金額	アカウント
1	122,345円	非表示
2	101,000円	非表示
3	98,780円	非表示
4	89,000円	非表示
5	86,000円	非表示
6	85,310円	非表示
7	85,284円	非表示
8	80,000円	非表示
9	78,800円	非表示
10	78,329円	非表示

この結果はこの結果として良いのだが、明らかに別アカウント同一人物が居る。
※アイコンのみでも分かるけど、名前にもそれが出てる。
Yotube側の制限によって、1日に送信可能なスーパーチャット金額は5万円までとされている。5万円より多く投げたい場合、サブアカウントを作って投げる、という行為が発生するためこうなる。これは、ランキング上位層のみで発生する事象なので、同一人物判定をするかしないかでランキング上位が変動する可能性が高い。パターンを洗い出して、同一人物判定をしてみる。

・パターン0：同一ID別表示名

・パターン1：別ID同一表示名

・パターン2：アカウント表示名との曖昧一致
アカウントα：文字列A
アカウントβ：文字列A文字列B
アカウントγ：文字列C文字列A文字列D

・パターン3：特定文字列の曖昧一致
アカウントα：文字列A文字列B
アカウントβ：文字列C文字列A
アカウントγ：文字列D文字列A文字列E

いろいろ検討してみたが、パターン3は対応しないことにした。名前を単語分割して一致率高いのを出してみたりしたが、出力を確認しても、結果が正しいかどうかの判定ができなかった。途中で人間判断とか入れたくない。自動化したい。
で、0，1，2対応をSQLで実現するのは厳しいので、c#で実装。
試行錯誤の末にパラメータも増えてコードがごちゃごちゃしてるが、とりあえずやりたいことは実現できた。

public class DtoPaidUser
{
    /// <summary>
    /// グルーピングKeyになるための最小文字数
    /// </summary>
    private const int KEY_NAME_MIN_COUNT = 3;

    /// <summary>
    /// グルーピングKeyになるための最小金額
    /// </summary>
    private const decimal KEY_AMOUNT_MIN = 10000;

    /// <summary>
    /// グルーピングValueになるための最小金額
    /// </summary>
    private const decimal VALUE_AMOUNT_MIN = 10000;

    /// <summary>
    /// この金額を超えるアイテムが無い限りグループとしない
    /// </summary>
    private const decimal GROUPING_AMOUNT_MIN = 45000;

    public long Rank { get; set; }
    public decimal Amount { get; set; }
    public string Name { get; set; }
    public string NameEdited { get; set; }
    public string Id { get; set; }
    public string Url0 { get; set; }
    public string Url1 { get; set; }
    public List<DtoPaidUser> InnerUserList { get; set; }

    /// <summary>
    /// 同一人物判定用文字列を設定する
    /// </summary>
    public void SetNameEdited()
    {
        NameEdited = Name.Replace(" ", string.Empty);
    }

    /// <summary>
    /// 同一人物判定
    /// </summary>
    /// <param name="dtoPaidUserList">同一人物判定されていないリスト</param>
    public static List<DtoPaidUser> GetPaidUserListWithInnerUserList(List<DtoPaidUser> dtoPaidUserList)
    {
        var result = new List<DtoPaidUser>(); 

        foreach (var item in dtoPaidUserList)
        {
            item.SetNameEdited();
        }

        //自分と同一だと思われるアイテムをValueに詰める
        var userList = new Dictionary<DtoPaidUser, List<DtoPaidUser>>();
        foreach (var user in dtoPaidUserList)
        {
            //結合Keyとなるユーザーの選別
            if (user.NameEdited.Length >= KEY_NAME_MIN_COUNT && user.Amount >= KEY_AMOUNT_MIN)
            {
                ///結合対象となるユーザーの選別
                ///（
                /// ・名前を含んでいる。
                /// かつ
                /// ・IDが自分では無い。
                /// かつ
                /// ・特定金額以上
                /// ）または
                /// （
                /// ・IDが自分と同じ
                /// かつ
                ///     (・名前が違う
                ///     または
                ///     ・サムネイル0が違う
                ///     または
                ///     ・サムネイル1が違う
                ///      )
                /// ）
                var q =
                    from innerUser in dtoPaidUserList
                    where
                        (
                            innerUser.NameEdited.Contains(user.NameEdited)
                            && !user.Id.Equals(innerUser.Id)
                            && innerUser.Amount >= VALUE_AMOUNT_MIN
                        )
                        || 
                        (
                            user.Id.Equals(innerUser.Id) 
                            && 
                                (
                                    !user.Name.Equals(innerUser.Name)
                                    || !user.Url0.Equals(innerUser.Url0)
                                    || !user.Url1.Equals(innerUser.Url1)
                                )
                        )
                    select innerUser;

                var preInnerList = q.ToList();
                if(preInnerList.Count != 0)
                {
                    decimal maxAmount = preInnerList.Max(x => x.Amount);

                    //グループ内に一定金額以上のアイテムが無ければ、同一とはみなさない。
                    if (maxAmount > GROUPING_AMOUNT_MIN || user.Amount > GROUPING_AMOUNT_MIN)
                    {
                        userList.Add(user, preInnerList);
                    }
                    else
                    {
                        userList.Add(user, new List<DtoPaidUser>());
                    }
                }else
                {
                    userList.Add(user, new List<DtoPaidUser>());
                }
            }
            else
            {
                userList.Add(user, new List<DtoPaidUser>());
            }
        }

        //結合対象数が多い順でソート
        var groupedUserDic = userList.OrderByDescending(pair => pair.Value.Count).ToList();

        //Valueに詰めた同一アイテムをKeyから削除
        int counter = 0;
        while (true)
        {
            if (groupedUserDic.Count <= counter)
            {
                break;
            }

            var pair = groupedUserDic.ElementAt(counter);
            for (int i = 0; i < pair.Value.Count; i++)
            {
                //内部要素になったアイテムをKeyから削除する
                groupedUserDic.Remove(
                    (from item in groupedUserDic
                     where pair.Value[i].Id.Equals(item.Key.Id)
                     select item).First());

            }

            counter++;
        }

        foreach (var keyValue in groupedUserDic)
        {
            keyValue.Key.InnerUserList = keyValue.Value;

            //TODO:名前ベースで詰める事になるが金額ベースで詰めるべきか検討
            result.Add(keyValue.Key);
        }

        return result;
    }
}

出力された同一人物リスト

ベースアカウント	同一人物判定アカウント
非表示	非表示 非表示 非表示
非表示	非表示 非表示
非表示	非表示 非表示
非表示	非表示 非表示
非表示	非表示 非表示
非表示	非表示 非表示
非表示	非表示
非表示	非表示
非表示	非表示
非表示	非表示
非表示	非表示

同一人物判定をしない『旧』と、同一人物判定をした『新』のラインキングを比較してみる。

順位	旧金額	旧アカウント	新金額	新アカウント
1	122,345円	非表示	182,345円	非表示 + SUB
2	101,000円	非表示	172,500円	非表示 + SUB
3	98,780円	非表示	163,000円	非表示 + SUB
4	89,000円	非表示	151,000円	非表示 + SUB
5	86,000円	非表示	140,000円	非表示 + SUB
6	85,310円	非表示	120,000円	非表示 + SUB
7	85,284円	非表示	98,780円	非表示
8	80,000円	非表示	98,400円	非表示 + SUB
9	78,800円	非表示	97,660円	非表示 + SUB
10	78,329円	非表示	91,000円	非表示 + SUB

思ったとおり上位層のランキングが結構動いた。

これでたいたい材料は揃ったかな、そろそろ見せ方の工夫とかを考える工程に入るか。
このサイト縦に長いから、横に広がる表作っても見えない、とか有るし。