周末的时间，我们在GitHub用什么语言编程？
jopen 8年前
<p style="text-align: center;"><a href="/misc/goto?guid=4958986207227852489" title="github"><img alt="周末的时间，我们在GitHub用什么语言编程？" src="https://simg.open-open.com/show/6a8dd9147ee3d646e9ccfef8f071d6d9.png" /></a></p> <p>村上春树（日本后现代主义作家）有一本书，《当我跑步时，我谈些什么》，Felipe 这篇文章的统计数据和这本书有点类似，周末的时间我们会使用什么语言进行编程，看了他的文章，我们会知道在办公室里使用的语言，不过是谋生工具而已，私底下大家并不喜欢它们。</p> <p>下面这张表格的内容是 2016 年度排名前 20 位的周末编程语言，根据 <a href="https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&ved=0ahUKEwj5ofjF8fHSAhVFVWMKHSmUCMwQFggnMAA&url=https://cloud.google.com/bigquery/&usg=AFQjCNHgAoo0xZ0h_6rmYL5C4N1NKI-ahQ">GitHub</a>（全球领先的软件开发平台）、<a href="/misc/goto?guid=4959005038555701331">GHTorrent</a>（监控着 GitHub 上的每一次更改，并将每一次更改的更新内容，以 JSON 字符串形式存储在 MongoDB 数据库）、<a href="https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&ved=0ahUKEwj5ofjF8fHSAhVFVWMKHSmUCMwQFggnMAA&url=https://cloud.google.com/bigquery/&usg=AFQjCNHgAoo0xZ0h_6rmYL5C4N1NKI-ahQ">BigQuery</a>（谷歌的数据仓库）等三个平台数据作为分析数据源。</p> <p style="text-align:center"><img alt="周末的时间，我们在GitHub用什么语言编程？" src="https://simg.open-open.com/show/3b08384f73d450232098ab679153646a.jpg" /></p> <p>从上面这张表格我们可以看到，排名前二十位的分别是：</p> <ol> <li><a href="https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=2&cad=rja&uact=8&ved=0ahUKEwi284Sa8vHSAhUW22MKHWtdDMMQFggmMAE&url=https://github.com/rust-lang/rust&usg=AFQjCNEvOLBGOlhzhk_8uthN_wRu_1foPA">Rust</a></li> <li><a href="https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=2&cad=rja&uact=8&ved=0ahUKEwivuoCr8vHSAhUO1WMKHRxMAOgQFggnMAE&url=https://en.wikipedia.org/wiki/OpenGL_Shading_Language&usg=AFQjCNEKO3BTMSJeMXzXDv3d6cB4IUFjMQ">Glsl</a>（以C语言为基础的高阶着色语言，它是由 OpenGL ARB 建立）</li> <li><a href="https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&ved=0ahUKEwjumJzw8vHSAhVDy2MKHX61Cp0QFggdMAA&url=https://dlang.org/&usg=AFQjCNGPVNBOLhv6wkWFnv34K4oYT_FF2Q">D</a>（类似于C语法的静态类型编程语言）</li> <li><a href="https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&ved=0ahUKEwjztqyJ8_HSAhXDKWMKHWzGDIgQFggaMAA&url=https://www.haskell.org/&usg=AFQjCNHxFF7x0nAVz6pJhSKEkZAp1lYS1A">Haskell</a>（函数型编程语言）</li> <li><a href="https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&ved=0ahUKEwii-pe08_HSAhUOw2MKHbSrCwMQFggdMAA&url=https://common-lisp.net/&usg=AFQjCNFDIW-ykWvKar2Wa5U4ScoiQqecew">Common Lisp</a>（Lisp 语言的分支）</li> <li><a href="https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=8&cad=rja&uact=8&ved=0ahUKEwjxlfbV8_HSAhUJzmMKHdfGAEwQFggxMAc&url=https://en.wikipedia.org/wiki/KiCad&usg=AFQjCNE2OA3f-C25AlWPOFHcHm6xIJrkmw">Kicad</a>（开源的电路自动设计语言）</li> <li><a href="https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&ved=0ahUKEwiZ7bPz8_HSAhUL6mMKHbliD0YQFggdMAA&url=https://www.gnu.org/s/emacs/manual/html_node/elisp/&usg=AFQjCNHgwbYb4CTG0dzzgfMIL6dfeyswFQ">Emacs Lisp</a>（Lisp 语言的分支）</li> <li><a href="https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&ved=0ahUKEwi105Sf9PHSAhUBDGMKHYNLCW4QFggaMAA&url=https://www.lua.org/&usg=AFQjCNErx6dOqg5an4-PTCIVbBw_Vow0kA">Lua</a>（轻量级的可扩展脚本语言）</li> <li><a href="https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=3&cad=rja&uact=8&ved=0ahUKEwjmt8a89PHSAhUN8mMKHcdRBs8QFggiMAI&url=http://www.schemers.org/&usg=AFQjCNF-Ewv1QNzqeQpfzTEotBr2C4ui9Q">Scheme</a>（函数式编程语言，是 Lisp 的两种主要方言之一，另一种为 Common Lisp）</li> <li><a href="https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&ved=0ahUKEwjXyZXP9PHSAhUS22MKHYChDTcQFggaMAA&url=http://julialang.org/&usg=AFQjCNF47iiW0ylaSeEf0dOl27c5kZ22MA">Julia</a>（针对数值计算的高性能动态编程语言）</li> <li><a href="https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&ved=0ahUKEwiQ77Lu9PHSAhUM6mMKHcPZADkQFggaMAA&url=http://elm-lang.org/&usg=AFQjCNEbyTAo2XqjYDmvePaR8MdvZ-p4vg">Elm</a>（编译为 JavaScript 的函数型语言）</li> <li>Eagle</li> <li><a href="https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=4&cad=rja&uact=8&ved=0ahUKEwjw-fml9fHSAhUMzmMKHVp2ABkQFggoMAM&url=https://racket-lang.org/&usg=AFQjCNEOFk-19iNJNGjZzqTWT140R5ULiQ">Racket</a>（LISP 家族中的一员）</li> <li><a href="https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=2&cad=rja&uact=8&ved=0ahUKEwjPnpS89fHSAhVT0WMKHcO2AGAQFggqMAE&url=https://www.dartlang.org/&usg=AFQjCNEUhMcqYeQ9t6cjfcx6KKdImFKrgw">Dart</a>（分布式编程语言，谷歌的很多应用程序都依赖于这个语言）</li> <li><a href="https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&ved=0ahUKEwjwgpfY9fHSAhVP72MKHS7WBg0QFggaMAA&url=http://nsis.sourceforge.net/&usg=AFQjCNEyuekHtWw7SRndFuNlzufn-DOMEA">Nsis</a>（全称 Nullsoft Scriptable Install System）</li> <li><a href="https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&ved=0ahUKEwit1PPw9fHSAhUUUGMKHW3dCRQQFggaMAA&url=https://clojure.org/&usg=AFQjCNH970FbG65WKqxMZnynYvARENGeiA">Clojure</a>（Lisp 语言的一种，可以运行在 Java 虚拟机上）</li> <li><a href="https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&ved=0ahUKEwiEssmT9vHSAhUW8GMKHWIQD48QFggdMAA&url=https://kotlinlang.org/&usg=AFQjCNGXKT03tgxJWb_HYfMeXhS5u0243A">Kotlin</a>（针对 JVM、安卓和浏览器设计的一种静态类型语言）</li> <li><a href="https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&ved=0ahUKEwja7pur9vHSAhVK8GMKHQkjBIoQFggdMAA&url=http://elixir-lang.org/&usg=AFQjCNFuxjfBTKvqhu_2giTJDplT6k_8Jw">Elixir</a>（运行在 Erlang VM，低延时语言）</li> <li><a href="https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&ved=0ahUKEwjEtYTX9vHSAhUW1GMKHeWjCmoQFggdMAA&url=http://www.ocaml.org/&usg=AFQjCNH2SPsXFQGEw3nnvz1lhEA9Jj9KxA">F#</a>（微软发展的为 .NET 语言提供运行环境的程序设计语言，属于函数型语言）</li> <li><a href="https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&ved=0ahUKEwjEtYTX9vHSAhUW1GMKHeWjCmoQFggdMAA&url=http://www.ocaml.org/&usg=AFQjCNH2SPsXFQGEw3nnvz1lhEA9Jj9KxA">Ocaml</a></li> </ol> <p>这个排名很清晰地告诉我们，2016 年属于 Functional Languages（函数型语言），以及 3D 世界的脚本语言。另外学习 Java 的朋友可以发现，Java 没有入选，但是很多函数型语言可以基于 JVM 运行，这给了我们一个提示，即未来 JVM 将会包容更多的语言，而不仅仅是 Java 语言本身。</p> <p>再来看看后 20 位语言，你会发现很多我们日常谋生的语言都在这里呢，包括：</p> <ul> <li>Nginx（高性能的 HTTP 和反向代理服务器，也是 IMAP/POP3/SMTP 服务器）</li> <li>Matlab（美国 MathWorks 公司出品的商业数学软件，用于算法开发、数据可视化、数据分析以及数值计算的高级技术计算语言和交互式环境）</li> <li>Processing（具有革命前瞻性的新兴计算机语言，是 Java 语言的延伸，并支持许多现有的 Java 语言架构，不过在语法（syntax）上简易许多，并具有许多贴心及人性化的设计）</li> <li>Vue（前端开发库，通过简洁的 API 提供高效的数据绑定和灵活的组件系统）</li> <li>Fortran（世界上最早出现的计算机高级程序设计语言，广泛应用于科学和工程计算领域）</li> <li>Visual Basic（这是我上大学时候最常用的语言，一种高级语言，具备可操作的 GUI 界面）</li> <li>Objective-C++（主要使用于 Mac OS X 和 GNUstep 这两个使用 OpenStep 标准的系统）</li> <li>Plsql（是一种程序语言，叫做过程化 SQL 语言（Procedural Language/SQL））</li> <li>Plpgsql</li> <li>Web Ontology Language（W3C 推荐的语义互联网中本体描述语言的标准）</li> <li>Smarty（使用 PHP 写出来的模板引擎，是目前业界最著名的 PHP 模板引擎之一）</li> <li>Groovy（基于 JVM（Java 虚拟机）的敏捷开发语言，它结合了 Python、Ruby 和 Smalltalk 的许多强大的特性，Groovy 代码能够与 Java 代码很好地结合，也能用于扩展现有代码）</li> <li>Batchfile</li> <li>Objective-C</li> <li>Powershell（命令行外壳程序和脚本环境，使命令行用户和脚本编写者可以利用 .Net Framework 的强大功能）</li> <li>Xslt（扩展样式表转换语言的外语缩写，这是一种对 XML（标准通用标记语言的子集）文档进行转化的语言，XSLT 中的T代表英语中的“转换”（Transformation））</li> <li>Cucumber（能够理解用普通语言描述的测试用例的支持行为驱动开发（BDD）的自动化测试工具，用 Ruby 编写，支持 Java 和·Net 等多种开发语言）</li> <li>Hcl</li> <li>Puppet（Linux、Unix、Windows 平台的集中配置管理系统，使用自有的 Puppet 描述语言，可管理配置文件、用户、cron 任务、软件包、系统服务等）</li> <li>Gcc Machine Description（GNU 编译器套件（GNU Compiler Collection）包括C、C++、Objective-C、Fortran、Java、Ada 和 Go 语言的前端，也包括了这些语言的库（如 libstdc++、libgcj 等等））。</li> </ul> <p style="text-align:center"><img alt="周末的时间，我们在GitHub用什么语言编程？" src="https://simg.open-open.com/show/8b2249b700fefe15c0d2be33117e8f34.jpg" /></p> <p>让我们看看这些 2016 年流行的周末“Happy Hour”语言的变化趋势，如图所示：</p> <p style="text-align:center"><img alt="周末的时间，我们在GitHub用什么语言编程？" src="https://simg.open-open.com/show/fb1f45a6e879071c720a748c0212a62e.jpg" /></p> <p>我们解释一下上面这张图所表达的含义：</p> <ul> <li>Rust 语言过去只在工作日使用，最近 6 年呈现爆发性增长，成为一门“周末”语言；</li> <li>越是流行，越是会成为一门“周末”语言；</li> <li>Puppet 语言周末大家用得很少，一直都很少；</li> <li>Ruby 正在逐渐和日常工作脱离，转而进入编程爱好者的周末乐园；</li> <li>R语言看得出来前两年（2014、2015）很火，但是 2016 年又回归到了中等热度；</li> <li>Haskell、Clojure，谁让人工智能这么火呢，这两门语言自然在周末也得多练习；</li> <li>Arduino 语言正在逐渐火起来；</li> <li>Python 和C，这几年还是有很多开发人员在使用，保持着持续流行状态。</li> </ul> <p>那么其他语言是如何按照流行程度排序的？请看下面这张图：</p> <p style="text-align:center"><img alt="周末的时间，我们在GitHub用什么语言编程？" src="https://simg.open-open.com/show/03b1ca6608f729f989e08f026828e927.jpg" /></p> <p>作者列举了一些来自 reddit 和 HN 的问题，并作出回复。</p> <p><a href="/misc/goto?guid=4959005040358688924">/u/techmidrop</a> 说：“我妈妈说了，我只能在星期天的下午 4 点到 6 点使用 Rust”。</p> <blockquote> <p>很多人和你一样！Rust 这几年来都位居周末语言榜首。但是回到 2010 年，它也是工作日语言之一。</p> </blockquote> <p><a href="/misc/goto?guid=4959005040446505718">/u/mooglinux 说</a>：“我现在需要 GitHub 出一份报告，报告中需要说明哪些语言分别在周末、工作日被提交的次数最多”。</p> <blockquote> <p>放心，会有的。</p> </blockquote> <p><a href="/misc/goto?guid=4959005040535926014">/u/MasterRaceLordGaben 说</a>：“谁在周末玩 Assembly？”</p> <blockquote> <p>也许是学生做家庭作业？Assembly 这门语言在这里其实不是很知名，也许是有人在 Stack<br /> Overflow 里搜索问题答案，其实并不是真的想写代码。</p> </blockquote> <p>作者使用 BigQuery 快速地分析了这些数据集，只用了 5 分钟时间。</p> <p>对 2016 年流行语言进行排名，代码如下：</p> <pre> <span style="color:#000000">#standardSQL </span><span style="color:#0000ff">SELECT</span><span style="color:#000000"> lang , </span><span style="color:#ff00ff">ROUND</span>(weekend<span style="color:#808080">/</span>weekday,<strong>2</strong><span style="color:#000000">) ratio , weekday, weekend , repos</span><span style="color:#ff0000">[</span><span style="color:#ff0000">OFFSET (0)</span><span style="color:#ff0000">]</span>.value sample_repo, repos<span style="color:#ff0000">[</span><span style="color:#ff0000">OFFSET (1)</span><span style="color:#ff0000">]</span><span style="color:#000000">.value sample_repo_2 </span><span style="color:#0000ff">FROM</span><span style="color:#000000"> ( </span><span style="color:#0000ff">SELECT</span> lang, <span style="color:#ff00ff">month</span><span style="color:#000000"> , </span><span style="color:#ff00ff">MAX</span>(<span style="color:#0000ff">IF</span>(weekday,c,<span style="color:#0000ff">null</span>)) weekday, <span style="color:#ff00ff">MAX</span>(<span style="color:#0000ff">IF</span>(<span style="color:#808080">NOT</span> weekday,c,<span style="color:#0000ff">null</span><span style="color:#000000">)) weekend , ANY_VALUE (repos) repos </span><span style="color:#0000ff">FROM</span><span style="color:#000000"> ( </span><span style="color:#0000ff">SELECT</span> language lang, TIMESTAMP_TRUNC (created_at, <span style="color:#ff00ff">YEAR</span>) <span style="color:#ff00ff">month</span><span style="color:#000000"> , EXTRACT (DAYOFWEEK </span><span style="color:#0000ff">FROM</span> a.created_at) <span style="color:#808080">BETWEEN</span> <strong>2</strong> <span style="color:#808080">AND</span> <strong>6</strong><span style="color:#000000"> weekday , </span><span style="color:#ff00ff">COUNT</span>(<span style="color:#0000ff">DISTINCT</span><span style="color:#000000"> committer_id) c , APPROX_TOP_COUNT (repo, </span><strong>3</strong><span style="color:#000000">) repos </span><span style="color:#0000ff">FROM</span> `ghtorrent<span style="color:#808080">-</span><span style="color:#000000">bq.ght_2017_01_19.commits` a </span><span style="color:#808080">JOIN</span> `fh<span style="color:#808080">-</span><span style="color:#000000">bigquery.github_extracts.ght_project_languages` b </span><span style="color:#0000ff">ON</span> a.project_id<span style="color:#808080">=</span><span style="color:#000000">b.project_id </span><span style="color:#0000ff">WHERE</span> b.<span style="color:#0000ff">percent</span><span style="color:#808080">></span><strong>0.25</strong> <span style="color:#808080">AND</span> EXTRACT (<span style="color:#ff00ff">YEAR</span> <span style="color:#0000ff">FROM</span> a.created_at) <span style="color:#808080">BETWEEN</span> <strong>2016</strong> <span style="color:#808080">AND</span> <strong>2016</strong> <span style="color:#0000ff">GROUP</span> <span style="color:#0000ff">BY</span> <strong>1</strong>,<strong>2</strong>,<strong>3</strong> <span style="color:#0000ff">HAVING</span> c<span style="color:#808080">></span><strong>100</strong><span style="color:#000000"> ) </span><span style="color:#0000ff">GROUP</span> <span style="color:#0000ff">BY</span> <strong>1</strong>,<strong>2</strong><span style="color:#000000"> ) </span><span style="color:#0000ff">WHERE</span> (weekend<span style="color:#808080">+</span>weekday)<span style="color:#808080">></span><strong>1450</strong> <span style="color:#0000ff">ORDER</span> <span style="color:#0000ff">BY</span> ratio <span style="color:#0000ff">DESC</span></pre> <p>对 2010-2016 年的数据画图，代码如下：</p> <pre> <span style="color:#000000">#standardSQL </span><span style="color:#0000ff">SELECT</span> <span style="color:#808080">*</span> <span style="color:#0000ff">FROM</span><span style="color:#000000"> ( </span><span style="color:#0000ff">SELECT</span> <span style="color:#808080">*</span>, <strong>40</strong><span style="color:#808080">-</span>rn inv_rank, ROW_NUMBER () <span style="color:#0000ff">OVER</span>(PARTITION <span style="color:#0000ff">BY</span> <span style="color:#ff00ff">month</span> <span style="color:#0000ff">ORDER</span> <span style="color:#0000ff">BY</span><span style="color:#000000"> ratio) weekend_rank, </span><span style="color:#ff00ff">MAX</span>(<span style="color:#ff00ff">month</span>) <span style="color:#0000ff">OVER</span>(PARTITION <span style="color:#0000ff">BY</span><span style="color:#000000"> lang) max_month </span><span style="color:#0000ff">FROM</span><span style="color:#000000"> ( </span><span style="color:#0000ff">SELECT</span> lang, <span style="color:#ff00ff">month</span><span style="color:#000000"> , </span><span style="color:#ff00ff">ROUND</span>(weekend<span style="color:#808080">/</span>weekday,<strong>2</strong><span style="color:#000000">) ratio , weekday, weekend, weekday</span><span style="color:#808080">+</span><span style="color:#000000">weekend total , repos</span><span style="color:#ff0000">[</span><span style="color:#ff0000">OFFSET (0)</span><span style="color:#ff0000">]</span><span style="color:#000000">.value sample_repo , ROW_NUMBER () </span><span style="color:#0000ff">OVER</span>(PARTITION <span style="color:#0000ff">BY</span> <span style="color:#ff00ff">month</span> <span style="color:#0000ff">ORDER</span> <span style="color:#0000ff">BY</span> weekday<span style="color:#808080">+</span>weekend <span style="color:#0000ff">DESC</span><span style="color:#000000">) rn </span><span style="color:#0000ff">FROM</span><span style="color:#000000"> ( </span><span style="color:#0000ff">SELECT</span> lang, <span style="color:#ff00ff">month</span><span style="color:#000000"> , </span><span style="color:#ff00ff">MAX</span>(<span style="color:#0000ff">IF</span>(weekday,c,<span style="color:#0000ff">null</span>)) weekday, <span style="color:#ff00ff">MAX</span>(<span style="color:#0000ff">IF</span>(<span style="color:#808080">NOT</span> weekday,c,<span style="color:#0000ff">null</span><span style="color:#000000">)) weekend , ANY_VALUE (repos) repos </span><span style="color:#0000ff">FROM</span><span style="color:#000000"> ( </span><span style="color:#0000ff">SELECT</span> language lang, TIMESTAMP_TRUNC (created_at, <span style="color:#ff00ff">YEAR</span>) <span style="color:#ff00ff">month</span><span style="color:#000000"> , EXTRACT (DAYOFWEEK </span><span style="color:#0000ff">FROM</span> a.created_at) <span style="color:#808080">BETWEEN</span> <strong>2</strong> <span style="color:#808080">AND</span> <strong>6</strong><span style="color:#000000"> weekday , </span><span style="color:#ff00ff">COUNT</span>(<span style="color:#0000ff">DISTINCT</span><span style="color:#000000"> committer_id) c , APPROX_TOP_COUNT (repo, </span><strong>3</strong><span style="color:#000000">) repos </span><span style="color:#0000ff">FROM</span> `ghtorrent<span style="color:#808080">-</span><span style="color:#000000">bq.ght_2017_01_19.commits` a </span><span style="color:#808080">JOIN</span> `fh<span style="color:#808080">-</span><span style="color:#000000">bigquery.github_extracts.ght_project_languages` b </span><span style="color:#0000ff">ON</span> a.project_id<span style="color:#808080">=</span><span style="color:#000000">b.project_id </span><span style="color:#0000ff">WHERE</span> b.<span style="color:#0000ff">percent</span><span style="color:#808080">></span><strong>0.25</strong> <span style="color:#808080">AND</span> language <span style="color:#808080">IN</span> UNNEST (SPLIT (<span style="color:#ff0000">'</span><span style="color:#ff0000">rust,haskell,c,clojure,arduino,ruby,python,go,r,puppet,xml</span><span style="color:#ff0000">'</span><span style="color:#000000">)) </span><span style="color:#808080">AND</span> EXTRACT (<span style="color:#ff00ff">YEAR</span> <span style="color:#0000ff">FROM</span> a.created_at) <span style="color:#808080">BETWEEN</span> <strong>2010</strong> <span style="color:#808080">AND</span> <strong>2016</strong> <span style="color:#0000ff">GROUP</span> <span style="color:#0000ff">BY</span> <strong>1</strong>,<strong>2</strong>,<strong>3</strong><span style="color:#000000"> ) </span><span style="color:#0000ff">GROUP</span> <span style="color:#0000ff">BY</span> <strong>1</strong>,<strong>2</strong><span style="color:#000000"> ) ) </span><span style="color:#0000ff">WHERE</span> rn<span style="color:#808080"><=</span><strong>40</strong><span style="color:#000000"> ) </span><span style="color:#0000ff">ORDER</span> <span style="color:#0000ff">BY</span> <strong>2</strong>,<strong>3</strong> <span style="color:#0000ff">DESC</span></pre> <p>外部查询语句：</p> <pre> <span style="color:#0000ff">SELECT</span><span style="color:#000000"> lang , EXTRACT (DAYOFWEEK </span><span style="color:#0000ff">FROM</span> date) <span style="color:#808080">BETWEEN</span> <strong>2</strong> <span style="color:#808080">AND</span> <strong>6</strong><span style="color:#000000"> weekday , </span><span style="color:#ff00ff">COUNT</span>(<span style="color:#0000ff">DISTINCT</span><span style="color:#000000"> email) c , APPROX_TOP_COUNT (repo, </span><strong>3</strong><span style="color:#000000">) repos </span><span style="color:#0000ff">FROM</span><span style="color:#000000"> ( </span><span style="color:#0000ff">SELECT</span> author.email, <span style="color:#ff00ff">LOWER</span>(REGEXP_EXTRACT (diff.new_path, r<span style="color:#ff0000">'</span><span style="color:#ff0000">\.([^\./\(~_ \- #]*)$</span><span style="color:#ff0000">'</span>)) lang, author.date, repo_name<span style="color:#ff0000">[</span><span style="color:#ff0000">OFFSET (0)</span><span style="color:#ff0000">]</span><span style="color:#000000"> repo </span><span style="color:#0000ff">FROM</span> `bigquery<span style="color:#808080">-</span><span style="color:#0000ff">public</span><span style="color:#808080">-</span>data.github_repos.commits`, UNNEST (<span style="color:#ff00ff">difference</span><span style="color:#000000">) diff </span><span style="color:#0000ff">WHERE</span> EXTRACT (<span style="color:#ff00ff">YEAR</span> <span style="color:#0000ff">FROM</span> author.date)<span style="color:#808080">=</span><strong>2016</strong><span style="color:#000000"> ) </span><span style="color:#0000ff">WHERE</span> lang <span style="color:#0000ff">IS</span> <span style="color:#808080">NOT</span> <span style="color:#0000ff">null</span> <span style="color:#808080">AND</span> LENGTH (lang)<span style="color:#808080"><</span><strong>8</strong> <span style="color:#808080">AND</span> REGEXP_CONTAINS (lang, <span style="color:#ff0000">'</span><span style="color:#ff0000">[a-zA-Z]</span><span style="color:#ff0000">'</span><span style="color:#000000">) </span><span style="color:#0000ff">GROUP</span> <span style="color:#0000ff">BY</span> <strong>1</strong>,<strong>2</strong> <span style="color:#0000ff">HAVING</span> c<span style="color:#808080">></span><strong>100</strong> <span style="color:#0000ff">ORDER</span> <span style="color:#0000ff">BY</span> c <span style="color:#0000ff">DESC</span></pre> <p>如何衡量数据：</p> <pre> <span style="color:#0000ff">SELECT</span><span style="color:#000000"> lang , </span><span style="color:#ff00ff">MAX</span>(<span style="color:#0000ff">IF</span>(weekday,c,<span style="color:#0000ff">null</span>)) weekday, <span style="color:#ff00ff">MAX</span>(<span style="color:#0000ff">IF</span>(<span style="color:#808080">NOT</span> weekday,c,<span style="color:#0000ff">null</span><span style="color:#000000">)) weekend , ANY_VALUE (repos) repos </span><span style="color:#0000ff">FROM</span><span style="color:#000000"> ( </span><span style="color:#0000ff">SELECT</span> JSON_EXTRACT_SCALAR (payload, <span style="color:#ff0000">'</span><span style="color:#ff0000">$.pull_request.head.repo.language</span><span style="color:#ff0000">'</span><span style="color:#000000">) lang , EXTRACT (DAYOFWEEK </span><span style="color:#0000ff">FROM</span> created_at) <span style="color:#808080">BETWEEN</span> <strong>2</strong> <span style="color:#808080">AND</span> <strong>6</strong><span style="color:#000000"> weekday , APPROX_TOP_COUNT (repo.name, </span><strong>3</strong><span style="color:#000000">) repos , </span><span style="color:#ff00ff">COUNT</span>(<span style="color:#0000ff">DISTINCT</span><span style="color:#000000"> actor.id) c </span><span style="color:#0000ff">FROM</span> `githubarchive.<span style="color:#ff00ff">year</span>.<strong>2016</strong><span style="color:#000000">` </span><span style="color:#0000ff">WHERE</span> type<span style="color:#808080">=</span><span style="color:#ff0000">'</span><span style="color:#ff0000">PullRequestEvent</span><span style="color:#ff0000">'</span> <span style="color:#0000ff">GROUP</span> <span style="color:#0000ff">BY</span> <strong>1</strong>,<strong>2</strong><span style="color:#000000"> ) </span><span style="color:#0000ff">GROUP</span> <span style="color:#0000ff">BY</span> <strong>1</strong></pre> <p>备注：这个帖子中，作者主要使用了最新的 <a href="/misc/goto?guid=4959005040630075361">GHTorrent import on BigQuery</a>，也许这些资料对读者也有用，<a href="/misc/goto?guid=4959005040727910407">GitHub Archive</a>、<a href="/misc/goto?guid=4959005040820966489">GitHub repos on BigQuery</a>、<a href="/misc/goto?guid=4959005040820966489">Stack Overflow on BigQuery</a>。</p> <p>原文地址：<a href="/misc/goto?guid=4959005040925947037">The top weekend programming languages — based on GitHub’s activity</a></p> <p><a href="/misc/goto?guid=4959005040820966489">40</a>: <a href="/misc/goto?guid=4959005041030013558">https://medium.com/google-cloud/github-on-bigquery-analyze-all-the-code-b3576fd2b150#.4wpjnqty9</a><a href="/misc/goto?guid=4959005040630075361">http://ghtorrent.org/gcloud.html</a></p> <p>来自: <a href="/misc/goto?guid=4959005041125892518" id="link_source2">InfoQ</a></p>