index.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "当前的算法是： 先用 BigQuery 把所有仓库按照最近两天的 star 总数排序，取出前 1000 条，此时仅有 repo_name 和 two_days_star_count 这两种信息。  然后对这 1000 条数据，分别用 API 查询到仓库创建时间和当前的 star 总数，按照其创建时间排序。\n",
    "\n",
    "因此可以认为：这样靠前的仓库，两天内积累的 star 比较多，而且建库比较晚"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "需要安装的主要有 \n",
    "1. GCP CLI工具  https://cloud.google.com/sdk/docs/install?hl=zh-cn\n",
    "2. 谷歌 BigQuery 客户端  https://cloud.google.com/bigquery/docs/reference/libraries?hl=zh-cn\n",
    "3. tqdm + pandas\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "请按照以上文档配置好 GCP 服务，主要是身份认证\n",
    "\n",
    "亲测, GCP 账号即使没有验证付款方式，也能顺利运行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/home/codespace/.python/current/lib/python3.12/site-packages/google/cloud/bigquery/table.py:1727: UserWarning: BigQuery Storage module not found, fetch data with the REST endpoint instead.\n",
      "  warnings.warn(\n"
     ]
    }
   ],
   "source": [
    "from google.cloud import bigquery\n",
    "from datetime import datetime, timedelta\n",
    "\n",
    "# Construct a BigQuery client object.\n",
    "client = bigquery.Client()\n",
    "\n",
    "# 获取今天的日期和前一天的日期\n",
    "today = datetime.today()\n",
    "yesterday = today - timedelta(days=1)\n",
    "\n",
    "# 将日期格式化为 BigQuery 查询需要的格式 (YYYYMMDD)\n",
    "today_str = today.strftime('%Y%m%d')\n",
    "yesterday_str = yesterday.strftime('%Y%m%d')\n",
    "\n",
    "# 构建查询字符串\n",
    "query = f\"\"\"\n",
    "WITH watch_data AS (\n",
    "  -- 查询最近一天的 WatchEvent 事件\n",
    "  SELECT \n",
    "    repo.name AS repo_name\n",
    "  FROM \n",
    "    `githubarchive.day.{today_str}`\n",
    "  WHERE \n",
    "    type = 'WatchEvent'\n",
    "  \n",
    "  UNION ALL\n",
    "  \n",
    "  -- 查询前一天的 WatchEvent 事件\n",
    "  SELECT \n",
    "    repo.name AS repo_name\n",
    "  FROM \n",
    "    `githubarchive.day.{yesterday_str}`\n",
    "  WHERE \n",
    "    type = 'WatchEvent'\n",
    ")\n",
    "\n",
    "SELECT \n",
    "  repo_name,\n",
    "  COUNT(*) AS star_count\n",
    "FROM \n",
    "  watch_data\n",
    "GROUP BY \n",
    "  repo_name\n",
    "ORDER BY \n",
    "  star_count DESC\n",
    "LIMIT 1000\n",
    "\"\"\"\n",
    "\n",
    "# 执行查询并等待结果\n",
    "rows = client.query(query)  # 执行查询\n",
    "results = rows.result().to_dataframe()  # 等待查询结果\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Fetching repo details:   0%|          | 0/1000 [00:00<?, ?it/s]"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Fetching repo details:   5%|▌         | 50/1000 [00:01<00:28, 33.57it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Repository data is None for PasinduSmarasingha/rust-h4ck-free\n",
      "Repository data is None for Kuyaa06/counter-str1ke-2-h4ck\n",
      "Repository data is None for xynqx/Roblox-Blox-Fruits-Script-2024\n",
      "Repository data is None for RoyalKnightD/Spotify-Premium-for-free-2024\n",
      "Repository data is None for DELDELHENRY2/r0b10x-synapse-x-free\n",
      "Repository data is None for 1ofzp1/Discord-AllinOne-Tool\n",
      "Repository data is None for carloscupu/IObit-Driver-Booster-Pro-2024-free-Serial-Key\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Fetching repo details:   6%|▌         | 55/1000 [00:01<00:33, 28.51it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Repository data is None for ArkManace/SketchUp-Pro-free-2024\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Fetching repo details:   7%|▋         | 68/1000 [00:02<00:31, 29.49it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Repository data is None for ArdaKundurayapan/Al-Photoshop-2024\n",
      "Repository data is None for XxLordxXx/Adobe-Express-2024\n",
      "Repository data is None for daveolio/counter-str1ke-2-h4ck\n",
      "Repository data is None for anthonnyjohn/hack-apex-1egend\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Fetching repo details:   8%|▊         | 76/1000 [00:02<00:31, 29.49it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Repository data is None for shadowstrike2/FL-Studio\n",
      "Repository data is None for CoderCosmics/Exit1ag-Free-2024\n",
      "Repository data is None for mersa31/ESET-KeyGen-2024\n",
      "Repository data is None for NoPressure000/Discord-AllinOne-Tool\n",
      "Repository data is None for dsdsadfdfs2323rw/Dayz-Cheat-H4ck-A1mb0t\n",
      "Repository data is None for SamsongT/h4ck-f0rtnite\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Fetching repo details:   8%|▊         | 85/1000 [00:03<00:29, 30.54it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Repository data is None for jubemar/League-0f-Legends-h4ck\n",
      "Repository data is None for brumikdev/IObit-Driver-Booster-Pro-2024-free-Serial-Key\n",
      "Repository data is None for modore/m0dmenu-gta5-free\n",
      "Repository data is None for shaikhhaareess/Roblox-Blox-Fruits-Script-2024\n",
      "Repository data is None for FusenTG/IDM-Activation-Script-2024\n",
      "Repository data is None for GunsJoez/SilenceGen\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Fetching repo details:  10%|▉         | 96/1000 [00:03<00:29, 30.80it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Repository data is None for JohnBluess/PhotoDiva-Pro-free-2024\n",
      "Repository data is None for anuj4207/SketchUp-Pro-free-2024\n",
      "Repository data is None for Luhbob/OpenSea-Bidding-Bot-2024\n",
      "Repository data is None for DuongSuper/Nexus-Roblox\n",
      "Repository data is None for Unknownshadowterror/NitroDreams-2024\n",
      "Repository data is None for hrjossmahmud/roblox-scr1pts-s0lara\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Fetching repo details:  10%|█         | 105/1000 [00:03<00:26, 33.57it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Repository data is None for DelDD/SoLBF\n",
      "Repository data is None for hyper8-codefien/Wave-Executor\n",
      "Repository data is None for popeyerollvn/cheat-escape-from-tarkov\n",
      "Repository data is None for Maurice1001/SonyVegas-2024\n",
      "Repository data is None for fafadk/hack-apex-1egend\n",
      "Repository data is None for Tyiscola/r0b10x-synapse-x-free\n",
      "Repository data is None for sabbir32981/minecraft-cheat2024\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Fetching repo details:  11%|█         | 109/1000 [00:03<00:29, 29.71it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Repository data is None for Roki0217/rust-h4ck-free\n",
      "Repository data is None for minhtan2010/Valorant-H4ck\n",
      "Repository data is None for ntziz2/Rainbow-S1x-Siege-Cheat\n",
      "Repository data is None for JojiDevelopment/Al-Photoshop-2024\n",
      "Repository data is None for yukihamaaa/Wemod-Premium-Unlocker-2024\n",
      "Repository data is None for eddadawdwda/counter-str1ke-2-h4ck\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Fetching repo details:  12%|█▏        | 117/1000 [00:04<00:27, 31.97it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Repository data is None for kaidenp11/Xbox-Game-Pass-Activator-Free-2024\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Fetching repo details:  12%|█▏        | 121/1000 [00:04<00:28, 30.82it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Repository data is None for JokenPlayz/Spotify-Premium-for-free-2024\n",
      "Repository data is None for Frankkubas123/Dayz-Cheat-H4ck-A1mb0t\n",
      "Repository data is None for lokinn005/Adobe-Express-2024\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Fetching repo details:  14%|█▍        | 139/1000 [00:04<00:31, 27.13it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Repository data is None for thisiscindychou/roblox-solara-executors\n",
      "Repository data is None for timigsh2mos/fortnite-hack-external\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Fetching repo details:  38%|███▊      | 383/1000 [00:13<00:23, 26.68it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Repository data is None for Dilodova/Roblox-Executor-Xeno-v1.0.9\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Fetching repo details:  50%|█████     | 502/1000 [00:17<00:17, 28.80it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Repository data is None for spear-blackseeker/Solara-Executor-Roblox\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Fetching repo details:  55%|█████▍    | 545/1000 [00:19<00:14, 30.80it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Repository data is None for beastamya8/Roblox-Synapse-X\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Fetching repo details:  56%|█████▌    | 556/1000 [00:19<00:13, 32.72it/s]"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Repository data is None for kotskirk852/verse-spoofer\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "Fetching repo details: 100%|██████████| 1000/1000 [00:34<00:00, 28.63it/s]\n"
     ]
    }
   ],
   "source": [
    "import requests\n",
    "import pandas as pd\n",
    "from tqdm import tqdm\n",
    "from concurrent.futures import ThreadPoolExecutor, as_completed\n",
    "\n",
    "\n",
    "# GitHub GraphQL API URL\n",
    "GRAPHQL_URL = \"https://api.github.com/graphql\"\n",
    "\n",
    "# GitHub Token (替换为你的 Token)\n",
    "TOKEN = \"your_token\"\n",
    "\n",
    "# 设置请求头\n",
    "HEADERS = {\n",
    "    \"Authorization\": f\"bearer {TOKEN}\",\n",
    "    \"Content-Type\": \"application/json\"\n",
    "}\n",
    "\n",
    "# 构建 GraphQL 查询模板\n",
    "GRAPHQL_QUERY_TEMPLATE = \"\"\"\n",
    "query {{\n",
    "  repository(owner: \"{repo_owner}\", name: \"{repo_name}\") {{\n",
    "    createdAt\n",
    "    stargazerCount\n",
    "  }}\n",
    "}}\n",
    "\"\"\"\n",
    "\n",
    "# 请求函数，获取仓库信息\n",
    "def fetch_repo_details(repo_name):\n",
    "    \"\"\"\n",
    "    使用 GitHub GraphQL API 获取仓库的创建日期和 star 总数。\n",
    "    \"\"\"\n",
    "    if \"/\" not in repo_name:\n",
    "        return None, None  # 无效的 repo_name\n",
    "    \n",
    "    repo_owner, repo_name_only = repo_name.split(\"/\", 1)\n",
    "    \n",
    "    query = GRAPHQL_QUERY_TEMPLATE.format(repo_owner=repo_owner, repo_name=repo_name_only)\n",
    "    \n",
    "    # 发送请求\n",
    "    try:\n",
    "        response = requests.post(\n",
    "            GRAPHQL_URL,\n",
    "            json={\"query\": query},\n",
    "            headers=HEADERS\n",
    "        )\n",
    "        # 检查请求是否成功\n",
    "        if response.status_code == 200:\n",
    "            data = response.json()\n",
    "            # 确保 data 和 repository 存在且有效\n",
    "            if \"data\" in data and \"repository\" in data[\"data\"]:\n",
    "                repo_data = data[\"data\"][\"repository\"]\n",
    "                if repo_data is not None:\n",
    "                    return repo_data.get(\"createdAt\"), repo_data.get(\"stargazerCount\")\n",
    "                else:\n",
    "                    print(f\"Repository data is None for {repo_name}\")\n",
    "            else:\n",
    "                print(f\"Missing 'data' or 'repository' for {repo_name}\")\n",
    "        else:\n",
    "            print(f\"Failed to fetch data for {repo_name}: {response.status_code}, {response.text}\")\n",
    "    except requests.exceptions.RequestException as e:\n",
    "        # 捕获请求中的异常\n",
    "        print(f\"Request failed for {repo_name}: {str(e)}\")\n",
    "    \n",
    "    # 出现问题时返回 None\n",
    "    return None, None\n",
    "\n",
    "# 处理并行化请求\n",
    "def fetch_repo_details_parallel(df):\n",
    "    results = []\n",
    "    \n",
    "    # 使用 ThreadPoolExecutor 进行并行化\n",
    "    with ThreadPoolExecutor(max_workers=10) as executor:\n",
    "        futures = {executor.submit(fetch_repo_details, row[\"repo_name\"]): index for index, row in df.iterrows()}\n",
    "        \n",
    "        # 显示进度条\n",
    "        for future in tqdm(as_completed(futures), total=len(futures), desc=\"Fetching repo details\"):\n",
    "            index = futures[future]\n",
    "            created_at, stargazer_count = future.result()\n",
    "            df.at[index, \"created_at\"] = created_at\n",
    "            df.at[index, \"current_star_count\"] = stargazer_count\n",
    "    \n",
    "    return df\n",
    "\n",
    "# 假设你已经得到了如下的 DataFrame\n",
    "df = results  # BigQuery 查询的结果 DataFrame\n",
    "\n",
    "# 添加两列：创建日期和 star 总数\n",
    "df[\"created_at\"] = None\n",
    "df[\"current_star_count\"] = None\n",
    "\n",
    "# 执行并行化的获取仓库信息\n",
    "df = fetch_repo_details_parallel(df)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "条目显示为 None 的，基本都是有 star 记录，但仓库被设为隐私或者删库的。这些仓库没删之前我点进去看到过，全都是不同账号创建的，但内容一模一样。没有代码，只有 Readme ，让人下载某个 exe 。 它们创建时间都非常相近，star 数量也非常接近。看仓库名也全都是奇奇怪怪的，不像给人看的。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "我怀疑是某个组织在社工投毒"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>repo_name</th>\n",
       "      <th>star_count</th>\n",
       "      <th>created_at</th>\n",
       "      <th>current_star_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Tencent/HunyuanVideo</td>\n",
       "      <td>2094</td>\n",
       "      <td>2024-11-28 08:38:31+00:00</td>\n",
       "      <td>2332</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LadybirdBrowser/ladybird</td>\n",
       "      <td>1705</td>\n",
       "      <td>2024-05-30 09:18:10+00:00</td>\n",
       "      <td>24964</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>huggingface/smol-course</td>\n",
       "      <td>1432</td>\n",
       "      <td>2024-11-25 19:22:43+00:00</td>\n",
       "      <td>1659</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>myhhub/stock</td>\n",
       "      <td>1030</td>\n",
       "      <td>2023-03-21 01:23:26+00:00</td>\n",
       "      <td>5273</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>lobehub/lobe-chat</td>\n",
       "      <td>937</td>\n",
       "      <td>2023-05-21 07:19:12+00:00</td>\n",
       "      <td>47133</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>995</th>\n",
       "      <td>CorentinTh/it-tools</td>\n",
       "      <td>28</td>\n",
       "      <td>2020-04-05 11:50:24+00:00</td>\n",
       "      <td>23318</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>996</th>\n",
       "      <td>PostHog/posthog</td>\n",
       "      <td>28</td>\n",
       "      <td>2020-01-23 22:46:58+00:00</td>\n",
       "      <td>22390</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>997</th>\n",
       "      <td>mifi/lossless-cut</td>\n",
       "      <td>28</td>\n",
       "      <td>2016-10-30 10:49:56+00:00</td>\n",
       "      <td>28316</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>998</th>\n",
       "      <td>interledger/open-payments-snippets</td>\n",
       "      <td>28</td>\n",
       "      <td>2023-09-21 12:29:10+00:00</td>\n",
       "      <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>999</th>\n",
       "      <td>doocs/advanced-java</td>\n",
       "      <td>28</td>\n",
       "      <td>2018-10-06 11:38:30+00:00</td>\n",
       "      <td>76521</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1000 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                              repo_name  star_count                created_at  \\\n",
       "0                  Tencent/HunyuanVideo        2094 2024-11-28 08:38:31+00:00   \n",
       "1              LadybirdBrowser/ladybird        1705 2024-05-30 09:18:10+00:00   \n",
       "2               huggingface/smol-course        1432 2024-11-25 19:22:43+00:00   \n",
       "3                          myhhub/stock        1030 2023-03-21 01:23:26+00:00   \n",
       "4                     lobehub/lobe-chat         937 2023-05-21 07:19:12+00:00   \n",
       "..                                  ...         ...                       ...   \n",
       "995                 CorentinTh/it-tools          28 2020-04-05 11:50:24+00:00   \n",
       "996                     PostHog/posthog          28 2020-01-23 22:46:58+00:00   \n",
       "997                   mifi/lossless-cut          28 2016-10-30 10:49:56+00:00   \n",
       "998  interledger/open-payments-snippets          28 2023-09-21 12:29:10+00:00   \n",
       "999                 doocs/advanced-java          28 2018-10-06 11:38:30+00:00   \n",
       "\n",
       "    current_star_count  \n",
       "0                 2332  \n",
       "1                24964  \n",
       "2                 1659  \n",
       "3                 5273  \n",
       "4                47133  \n",
       "..                 ...  \n",
       "995              23318  \n",
       "996              22390  \n",
       "997              28316  \n",
       "998                 35  \n",
       "999              76521  \n",
       "\n",
       "[1000 rows x 4 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 确保 created_at 是 datetime 类型\n",
    "df[\"created_at\"] = pd.to_datetime(df[\"created_at\"])\n",
    "\n",
    "# 按照 created_at 升序排序\n",
    "df_sorted = df.sort_values(by=\"created_at\", ascending=False)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sorted.to_csv('result.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}