工作中使用SQL如何让我成为一个优秀的科学家

Avatar 6月前 ⋅ 237 阅读

概要:导读:一篇关于为什么开始使用,以及怎样使用SQL的说明。SQL(结构化查询语言)在关系数据库管理是如何被应用到日常科学研究数据管理体系当中的?以下是一个SQL实战应用案例。   An explanation of why and ho...

导读:一篇关于为什么开始使用,以及怎样使用SQL的说明。SQL(结构化查询语言)在关系数据库管理是如何被应用到日常科学研究数据管理体系当中的?以下是一个SQL实战应用案例。

 

An explanation of why and how I started using SQL

一篇关于我为什么开始使用以及怎样使用SQL的说明

Zach Monge

Mar 5

 

SQL (Structured Query Language) is a computer language for relational database management and data manipulation. Relational databases and SQL are extremely popular in industry and for a good reason. Relational databases are great when working with large and complex databases. SQL as a language, allows you to efficiently query these databases. SQL is a declarative programming language, which basically means that when writing SQL code you know what it does but you don’t necessarily know how it works. Why SQL is such a powerful language happens all behind the scenes, where it can query databases with extreme efficiency. Since it is a declarative language, if you are familiar with imperative programming languages (e.g., Python), you will find it quite easy to learn. Again, with SQL, groups of individuals dedicated to learning how to efficiently query databases have done the hard work for us and figured out methods to query databases. With SQL, we just tell the computer what we want done.

SQL(结构化查询语言)是用于关系数据库管理的计算机语言和数据操作。关系数据库和SQL在行业中非常受欢迎是有它的原因的,在处理大型复杂数据库时,关系数据库非常有用。SQL作为一种语言,允许您有效地查询这些数据库。 SQL是声明性的编程语言,这基本上意味着在编写SQL代码时,你知道它的作用,但你不一定需要知道它是如何工作的。为什么SQL有如此强大的使用范围,让它可以极其高效地查询数据库?由于它是一种声明性语言,如果您熟悉命令式编程语言(例如Python),您会发现SQL很容易学习。再者,通过使用SQL,致力于学习如何有效地查询数据库的团队为我们完成了艰苦的工作,并找出了查询数据库的方法。使用SQL,我们只是告诉计算机我们想要做什么就可以了。

When I first learned about SQL, I did not think it would be useful for my day-to-day work as a graduate student studying computational cognitive neuroscience. I recognized that since SQL is so popular in industry, I would have to learn it, but I did not intend to use SQL as a student. After thinking a little bit more about how SQL could be used in my workplace, I realized that creating/maintaining relational databases could be extremely useful in my work.

当我第一次了解到SQL时,我不认为它对我作为一个研究计算认知神经科学的研究生的日常工作有什么用处。当我意识到SQL在行业中非常流行时,我想要学习它,但做为学生的时候我没想着去使用SQL。在思考了一下如何让SQL在工作中发挥作用后,我意识到创建/维护关系数据库对我的工作可能会非常有用。

 

Why I Chose to Start Using SQL

为什么我会选择开始使用SQL

 

My research investigates a discipline called the cognitive neuroscience of aging, meaning I typically conduct human brain imaging research studies containing samples of human research participants of younger adults (~18–29 years old) and older adults (~65–79 years old). Regarding specifically the older adult participants, it is actually somewhat difficult to find participants that (1) want to participate in research studies that typically span several days and (2) receive an MRI which can last anywhere from 1–2 hours. Therefore, it is more or less the same older adults that participate in my studies and other studies within the lab. Although each study design is different, there are typically similar components of each study. For example, typically all of the studies within the lab contain the same neuropsychological assessments. We are essentially conducting a longitudinal study but don’t even realize it.

我的研究了一门名为衰老的认知神经科学的学科,这意味着我通常会进行人脑成像调查,其中包括年轻人(18-29)和老年人(65-79)参与者的人群调查样本。具体来说,对于年长的参与者来说,事实上很难找到,因为(1)想要参加长时间跨越几天的调查研究的人不多,参与者需要配合做个核磁共振成像,这个需要1-2小时。因此,在实验室参与到我这个课题研究还有其他课题研究的,或多或少总是同一波老年人。尽管每次的研究课题设计的不一样,但有些元素是基本保持一致的。例如,通常实验室内所有研究课题都包含神经心理学评估。我们本质上是在进行一项纵向研究,但甚至都没有意识到这一点。

Even though we are using the same participants year after year, within the lab there was not a good system to track what participants are completing each of our studies. Essentially every study was independent of each other. It truly was a shame that there was no system in place to track participants over time. That’s when I realized that creating a standardized lab-wide relational database management system has the potential to transform my research. I could create a way to easily track participants over time and maintain their data all in one place. There are several benefits for creating this database management system. First, myself or one of my colleagues could conduct a number of interesting analyses, including tracking participants’ cognitive performance (e.g., memory) over time and see who exhibits cognitive decline, who does not, and what studies have they participated in. Furthermore, creating a relational database management system may save time administering assessments. A colleague and myself may be running two different studies around the same time and administering the same neuropsychological battery. If the same participants complete each study, they should not be given the neuropsychological battery each time in order to avoid practice effects. Lastly, we could collapse data between studies. For example, I predominantly conduct brain imaging studies but some of my colleagues just conduct behavioral studies. A colleague could query the relational database, see that many of his or her participants completed my study, and possibly correlate the behavioral measures with my imaging measures, or vice versa. In sum, the creation of a relational database system has the potential to increase the impact of my work.

尽管我们年复一年地使用同样的参与者,但在实验室内并没有一个很好的系统,来跟踪参与者是否正在完成我们的每一项研究。基本上每项研究都是在互相依赖。真正令人遗憾的是,没有一套到位的系统来跟踪参与者的情况。那时我意识到创建一个标准化的,实验室范围内的关系数据库管理 系统或许可以让我的研究工作大为改观,我可以创造一种方法来轻松地分时段的跟踪参与者,并将他们的数据保存在一个地方。创建这些数据系统有很多好处。首先,我本人或我的一位同事可以进行一些有趣的分析,包括跟踪参与者的认知表现(例如,记忆)。 看看谁的认知能力下降,谁没有,以及他们参与了哪些研究。此外,创建关系数据库管理系统可以节省管理评估的时间。一个 同事和我可能在同一时间进行两项不同的研究,并使用相同的神经心理成套测验。为了避免操作影响,不用在每次研究中都让改参与者完成神经心理成套测验。最后,我们可以在研究之间分解数据。例如,我主要从事脑成像研究,而我的同事只是做行为研究。同事可以查询关系数据库,可以看到他或她的许多也参与了我发起的研究的参与者的数据,并可能将行为测试数据与我的做影像测量的数据关联起来,反之亦然。总之,创建关系数据库系统可能会增加我所做的工作对其他人同事的影响。

 

Creating the Database

创造数据库

 

Now that I have made an argument for why I think the creation of the database is beneficial for my work, I’m going to explain how I made this database. I am a beginner to the design of relational databases, so I welcome any feedback. Before creating the database, it is important to create a diagram of the database structure. There are many programs available to do this, but I used DBDiagram, which is completely free. Below is the diagram:

既然我已经论证了为什么我认为创建数据库对我的工作是有益的,我将解释我是如何创建这个数据库的。我是关系数据设计的初学者。 所以我欢迎任何反馈。在创建数据库之前,创建数据库结构图是很重要的。有许多程序可用于此,而我使用了完全免费DDBDiagram,下图是具体构图:

The details of this diagram are not too important, but essentially each participant is assigned a unique participant id and each study is assigned a unique study id. The NIH Toolbox is the neuropsychological battery and there are a few other assessments in the ScreeningQuestionnaire.

这个图表的细节并不太重要,但基本上每个参与者都被分配了一个唯一的参与者ID,而每个研究被分配了一个唯一的研究IDNIH工具箱是神经心理成套测试,还有其他一些筛选问卷中的测试评估。

To actually create the database I used SQLite and specifically sqlite3 in Python. In order to ensure the data was entered properly, I viewed the database using DB Browser for SQLite. For readers interested in learning SQL, there are a lot of online resources, but I specifically used a Coursera classPython for Everybody and, most importantly, practiced with my own data!I am not able to make the data publicly available because it contains participants’ personal information, but you may view the script used to create the database on my GitHub. As of now the database only contains one study, but eventually it will be expanded to include multiple studies and more study data points.

要真正创建数据库,我使用了SQLite,特别是Python中的sqlite 3。为了确保正确输入数据,我使用SQLite数据库浏览器(DB4S)查看数据库。对于有兴趣学习SQL的读者来说,有很多在线资源,但我专门学习了一个新的在线Coursers课程——Python for Everybody,最重要的是,我用自己的数据进行了实践!我不能够让数据是公开的,因为它包含参与者的个人信息,但是您可以查看那个用于在我的 GitHub上创建数据库的脚本。到目前为止,尽管数据库只包含一项研究,但最终它将扩大到包括多项研究和更多的研究数据点。

 

Conclusions

最后总结

 

I am confident that my colleagues and myself will benefit from this database. I am excited to see what novel analyses this database will help yield! Lastly, I will end with one last piece of advice, which is if you think you cannot benefit from the use of a relational database, think again. Database management is important at any institution and has the potential to make your life easier and increase the quality of your work.

我相信,我的同事和我本人将从这个数据库中受益。我很高兴看到这个数据库会产生什么样的新分析!最后,我将给出最后一条建议:如果您认为不能从使用关系数据库中获益,那么请重新考虑。数据库管理在任何机构都是很重要的,并且有可能使你的生活变得更容易和更好,并且提高你的工作质量。


全部评论: 0

    我有话说: