Detailed Description
In order to make the objects, technical solutions and advantages of the embodiments of the present application more clear, the technical solutions of the embodiments of the present application will be clearly and completely described below with reference to the accompanying drawings in the embodiments of the present application.
For convenience of understanding, the terms involved in the embodiments of the present application are explained below:
Cloud technology (Cloud technology) refers to a hosting technology for integrating hardware, software, network and other series resources in a wide area network or a local area network to realize calculation, storage, processing and sharing of data. The cloud technology is a generic term of network technology, information technology, integration technology, management platform technology, application technology and the like based on cloud computing business model application, can form a resource pool, and is flexible and convenient as required. Cloud computing technology will become an important support. Background services of technical networking systems require a large amount of computing, storage resources, such as video websites, picture-like websites, and more portals. Along with the high development and application of the internet industry, each article possibly has an own identification mark in the future, the identification mark needs to be transmitted to a background system for logic processing, data with different levels can be processed separately, and various industry data needs strong system rear shield support and can be realized only through cloud computing.
The Database (Database), which can be considered as an electronic filing cabinet, is a place for storing electronic files, and users can perform operations such as adding, inquiring, updating, deleting and the like on the data in the files. A "database" is a collection of data stored together in a manner that can be shared with multiple users, with as little redundancy as possible, independent of the application.
The Database management system (DBMS for short, english: database MANAGEMENT SYSTEM) is a computer software system designed for managing databases, and generally has basic functions of storage, interception, security assurance, backup and the like. The database management system may classify according to the database model it supports, such as relational, XML (Extensible Markup Language ), or according to the type of computer supported, such as server clusters, mobile phones, or according to the query language used, such as SQL (structured query language (Structured Query Language), XQuery), or according to the energy impact focus, such as maximum scale, maximum speed, or other classification means.
SQL (Structured Query Language ) is a special purpose programming language, a database query and programming language, used to access data and query, update, and manage relational database systems. Common SQL statements include mysql, postgresql, oracle, sqlserver, etc.
The core instruction of the query statement is SELECT, and various auxiliary instructions including FROM, WHERE, GROUP BY, ORDER BY and the like are added for fine query.
The field is that each row in the table is called a "record", each record contains all the information in the row, just like a person in the address book database, but the record has no special record name in the database, and the number of records is often indicated by the row in which it is located. A field is a smaller unit than a record, and a collection of fields forms a record, each field describing a feature of the document, namely a data item, and having a unique field identifier for computer identification.
When the query results are displayed, the database will display the output columns specified after each SELECT, in some cases the displayed column names will be long or not intuitive enough, and then column aliases may be specified.
Regular expressions, also known as regular expressions. (English: regular Expression, often abbreviated in code as regex, regex p or RE), a concept of computer science. Regular expressions are typically used to retrieve, replace, text that meets a certain pattern (rule).
View refers to a view in a computer database, which is a virtual table whose contents are defined by queries. As with the real table, the view contains a series of columns and rows with names. But the view does not exist in the database in the form of a stored set of data values. The rows and columns are from tables referenced by queries defining the view and are dynamically generated when the view is referenced.
A Client (Client), or Client, refers to a program that corresponds to a server and provides local services to the Client. Except for some applications that only run locally, they are typically installed on a common client and need to run in conjunction with the server. After development of the internet, more commonly used clients include web browsers such as those used by the world wide web, email clients when receiving and sending email, and client software for instant messaging. For this type of application, there is a need for a corresponding server and service program in the network to provide corresponding services, such as database service, email service, etc., so that a specific communication connection needs to be established between the client and the server to ensure the normal operation of the application.
Any number of elements in the figures are for illustration and not limitation, and any naming is used for distinction only and not for any limiting sense.
In a specific practical process, data queried based on SQL input by a user is often used as a data source of a visual report, and a view table is generated and stored locally, so that the user can directly query the data from a locally stored view table by using a new SQL statement, and secondary processing of the data in the view table, such as query, screening, grouping, sorting and the like, is realized, and a better quality data analysis result is obtained. However, once the data in the original database changes, the data in the view chart needs to be synchronously updated, so that a scheme for inquiring the data from the view chart stored locally needs to be used for making related algorithm strategies for continuously updating the data, the implementation is complex, and the local storage needs to consume a large amount of storage resources, which all result in heavy projects and poor portability.
When the user needs to perform operations such as inquiring, screening, grouping, sorting and the like on the data in the view chart, a first inquiry statement for indicating to inquire the appointed data from the view chart can be input, the first inquiry statement is modified based on the original inquiry statement, a second inquiry statement for indicating to inquire the appointed data from the appointed database is obtained, namely, a data source of the inquiry is modified to the appointed database in the original inquiry statement, and then the data is directly inquired from the appointed database. According to the data query method, when data processing is performed based on the view table, data can be directly queried from the appointed database, so that the data in the original database and the view table do not need to be synchronously updated, a complex data sustainable updating algorithm strategy does not need to be developed and maintained, the data in the view table does not need to be stored locally, and storage resources are saved. The data query system realized based on the data query method has low realization complexity and strong portability.
After the design idea of the embodiment of the present application is introduced, some simple descriptions are made below for application scenarios applicable to the technical solution of the embodiment of the present application, and it should be noted that the application scenarios described below are only used for illustrating the embodiment of the present application and are not limiting. In the specific implementation, the technical scheme provided by the embodiment of the application can be flexibly applied according to actual needs.
Referring to fig. 1, an application scenario diagram of a data query method according to an embodiment of the present application is shown. The application scenario comprises a terminal device 101, a server 102 and a plurality of databases 103. The terminal device 101 and the server 102, and each database 103 may be connected through a wireless or wired network, and the terminal device 101 includes, but is not limited to, electronic devices such as a smart phone, a notebook computer, a desktop computer, a tablet computer, a media player, an intelligent wearable device, an intelligent sound box, and an intelligent television. The server 102 and the database 103 may be independent physical servers, or may be a server cluster or a distributed system formed by a plurality of physical servers, or may be a cloud server that provides cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communication, middleware services, domain name services, security services, CDNs, and basic cloud computing services such as big data and artificial intelligence platforms.
The terminal device 101 is provided with a data analysis client, a user can edit a user-defined query sentence through the data analysis client, the query sentence can comprise a query field, a database, query conditions and the like, the edited query sentence is sent to the server 102, the server 102 obtains data which are related to the query field and meet the query conditions from the corresponding database 103 based on the query sentence, the queried data is returned to the terminal device 101, and the data analysis client in the terminal device 101 displays the queried data in a certain mode. The user can also obtain more targeted and more refined data analysis results by carrying out secondary query based on the queried data or screening, grouping, sorting and the like on the queried data through the data analysis client.
The user may log in the data analysis website through the terminal device 101, and obtain the data analysis service through the web page, and the specific implementation manner of the web page end is similar to that of the data analysis client, which will not be described again.
Of course, the method provided by the embodiment of the present application is not limited to the application scenario shown in fig. 1, but may be used in other possible application scenarios, and the embodiment of the present application is not limited. The functions that can be implemented by each device in the application scenario shown in fig. 1 will be described together in the following method embodiments, which are not described in detail herein.
In order to further explain the technical solution provided by the embodiments of the present application, the following details are described with reference to the accompanying drawings and the detailed description. Although embodiments of the present application provide the method operational steps shown in the following embodiments or figures, more or fewer operational steps may be included in the method, either on a routine or non-inventive basis. In steps where there is logically no necessary causal relationship, the execution order of the steps is not limited to the execution order provided by the embodiments of the present application.
The data query method of the embodiment of the application can be applied to the data analysis client side in the terminal equipment and can also be applied to the webpage side. The technical solution provided by the embodiment of the present application is described below with reference to an application scenario shown in fig. 1, taking an application to a data analysis client as an example.
Referring to fig. 2, an embodiment of the present application provides a data query method, which specifically includes the following steps:
S201, a first query statement for indicating to query specified data from a view table is obtained, wherein the view table is generated based on an original query statement for indicating to query the specified data from a specified database.
The query sentence in the embodiment of the present application includes a SELECT clause (i.e., a query field clause), a FROM clause (i.e., a data source clause), a WHERE clause (i.e., a condition clause), a GROUP BY clause, an ORDER BY clause, and the like. The embodiment of the application mainly relates to a query field clause, a data source clause and a condition clause, wherein the query field clause is a character string between keywords SELECT and FROM in the query statement, the data source clause is a character string between the keywords FROM and WHERE, and the condition clause is a character string after keywords WHER. The query field clause comprises at least one query field, the data source clause comprises a database used when data are queried, the condition clause comprises at least one query condition, after receiving the query statement sent by the data analysis client, the server queries data corresponding to the query field from a designated database in the data source clause, screens the queried data based on the query condition, and returns the data meeting the query condition to the data analysis client. Of course, the user may not set the query condition, and the server directly returns the data corresponding to the query field to the data analysis client.
The following is one example of a query statement:
In some cases, the field names of the data displayed in the data table are long or not intuitive enough, in order to make the data table more concise and more readable, the corresponding alias fields may be set for the field names, the alias fields are displayed in the data table, the field names corresponding to the alias fields are original fields, for example, the original fields are date_format (create_time, '%y-%m-%d'), and the corresponding alias fields may be set as create_date. Thus, these query fields in the query field clause can be divided into two categories, one category containing the original field and the alias field, such AS "date_format (create_time, '% Y-%m-% -d') AS create_date", the original field being date_format (create_time, '% Y-% -m-%) d'), the alias field being create_date being displayed in the data table, and the other category containing the query field without the alias being set, i.e. containing only the original field, such AS api_name, page_id, etc. in the query statement example described above, the original field being displayed directly in the data table.
In particular, before executing step S201, the user may open the data analysis client, enter the data table management page, and the interface diagram of the data table management page may refer to fig. 3A, click the new data table button 301 in the data table management page 30, and display the new data table page 302 as shown in fig. 3B. After the user inputs the original query sentence in the SQL sentence column and clicks the parse button 303, the data analysis client parses the alias field and the original field that does not contain the alias field in the SELECT clause from the original query sentence, so that the user can SELECT the fields needed in the subsequent processing or SELECT the full selection. In addition, the detail information of each parsed field can be selected to be checked, and corresponding Chinese name, field type, remarks, format and other information can be set, and for the field with the Chinese name, the corresponding Chinese name is displayed in the subsequent processing process, so that the user operation is convenient, and the readability of the generated data table is improved. After the user determines that the parsed fields are correct and sets the detailed information corresponding to each field, clicking the determination button 304 triggers the data analysis client to create a view table for the original query statement, and associates and stores the original query statement, the fields obtained by parsing, the Chinese names corresponding to the fields, the corresponding relations between the original fields and the alias fields, and the like. Referring to fig. 3B, the user may further set a folder corresponding to the view table in the newly created data table page, store the various types of information to be stored in association in the folder, and when the user processes the view table, the data analysis client may obtain relevant information from the folder corresponding to the view table.
Referring to fig. 3C, for an edit page corresponding to a view table, fields parsed from an original query sentence corresponding to the view table, such as "uv", "creation date", "api_name", etc., are displayed in a left column 305, and if a corresponding chinese name is set for a field by a user during parsing, a corresponding chinese name, such as a chinese name whose "creation date" is the field "create_date", may be displayed in the left column. The user may select a field from the left column that needs to be used when drawing the data table, and add the selected field to the dimension column 306, the numeric column 307, or the condition column 308. The field corresponding to the horizontal axis of the data table in the dimension field 306, and the field added to the dimension field 306 BY the user appears in the GROUP BY clause in the first query statement. The value column 307 is a field corresponding to the vertical axis of the data table, one or more fields may be added in the value column 307, and the user may set an aggregation function corresponding to the field in the value column 307, and perform correlation operation on data corresponding to the field based on the aggregation function to obtain a corresponding value, where the aggregation function includes, but is not limited to, a summation function (Sum), an Average function (Average), a maximum function (Max), a minimum function (Min), a Count function (Count), and the like. The condition field 308 is a screening condition when screening data, one or more screening conditions may be set in the condition field 308, and a field is added to the condition field 308 to edit the WHERE clause in the first query sentence, for example, a field "creation date" is added to the condition field 308, and the set screening condition is that the creation date is 2020, 4, 1, to 2020, 5, 30, and only data with the creation date of 2020, 4, 1, to 2020, 5, 30 is acquired, and the data table 309 is drawn based on these data. Of course, the screening conditions may not be set in practical application. After the user sets the dimension bar 306, the numerical value bar 307 and the condition bar 308, clicking the finish button 310 to trigger the data analysis client to generate a first query statement according to fields in the dimension bar 306, the numerical value bar 307 and the condition bar 308, wherein a data source in the first query statement is a view table, a query field in the SELECT clause is a field selected from the left bar 305, and a query condition in the WHERE clause is a screening condition set in the condition bar 308, so that the first query statement is used for indicating specified data which corresponds to the query field in the view table and satisfies the screening condition, drawing of the data table is finished based on the obtained specified data, and the drawn data table 310 is displayed in an editing page.
S202, modifying the first query statement based on the original query statement to obtain a second query statement for indicating to query the specified data from the specified database.
In specific implementation, the data analysis client extracts the name of the view chart from the data source clause in the first query statement, acquires the original query statement corresponding to the view chart, extracts the data source (namely the appointed database) in the original query statement, modifies the data source in the first query statement into the appointed database extracted from the original query statement, and takes the modified first query statement as the second query statement. Because the data source in the second query statement is the appointed database, the appointed data can be directly obtained from the appointed database based on the second query statement without storing the data corresponding to the view chart, and the data in the view chart is not required to be updated.
S203, acquiring specified data based on the second query statement.
In the implementation, the data analysis client sends the second query statement to the server, and the server acquires the specified data from the specified database based on the second query statement and sends the acquired specified data to the data analysis client. And the data analysis client draws and displays a corresponding data table based on the specified data returned by the server. An example of one data table plotted is given in fig. 3C, the horizontal axis of which is the creation date, and the vertical axis of which includes uv (deduplication value) and pv (count value) obtained based on the queried data.
According to the data query method provided by the embodiment of the application, when the data processing is carried out based on the view table, the first query statement aiming at the view table is modified based on the original query statement corresponding to the view table, the second query statement capable of directly querying data in the appointed database of the original query statement is obtained, and the data is directly obtained from the appointed database based on the second query statement, so that the data in the view table is not required to be stored locally, namely the changed data in the appointed database is not required to be updated into the view table, and a complex data sustainable updating algorithm strategy is not required to be developed and maintained, a large amount of local storage resources are also saved, the complexity of a data analysis client and a server is reduced, and the portability is strong.
In practical application, if the GROUP BY clause, the ORDER BY clause, the with clause, and the union clause-moving clause already exist in the original query statement, when the first query statement is generated based on the operation of the user, the GROUP BY clause is added, and the result set and the data sequence of the final query result are changed. For this reason, before executing step S201, it is also necessary to confirm whether the original query sentence includes the GROUP BY clause, the ORDER BY clause, the with clause, and the union clause-moving clause, and if the clause is not included, step S201 is executed, and if any of the clauses is included, the data query is performed using the sub-query method. The sub-query mode is to obtain data from a specified database based on an original query statement, and then query the specified data from the obtained data, for example, the original query statement is selected a, sum (b), sum (c) FROM table GROUP BY a, the sub-query statement is ：SELECTA,B FROM(select a as A,sum(b)as B,sum(c)as C FROM table)as table_alias GROUP BY A ORDER BY B;, and the second query statement obtained by the method of the embodiment of the application is SELECT A AS A, sum (b) as B FROM table GROUP BY a ORDER BY sum (b).
In order to make the finally generated data table more concise and more readable, the user can set a corresponding alias field for the original field in the original query statement, and the subsequent data analysis client replaces the original field with the alias field of the original field, for example, displays the alias field corresponding to the original field, and the query field in the first query statement generated based on the user operation is also the alias field corresponding to the original field. However, the alias field is user-defined, the alias field does not exist in the appointed database, and corresponding data cannot be queried based on the alias field, so that the alias field in the first query statement needs to be replaced by the corresponding original field. Specifically, step S202 includes replacing the alias field in the first query statement with the corresponding original field according to the correspondence between the token original field and the alias field obtained from the original query statement, replacing the view table in the first query statement with the specified database, and adding the query condition in the original query statement to the condition clause of the first query statement. The first query statement modified in the above manner is the second query statement.
In particular, the SELECT clause, WHERE clause, GROUP BY clause, and ORDER BY clause generally include alias fields, and it is necessary to identify the alias fields in the SELECT clause, WHERE clause, GROUP BY clause, and ORDER BY clause in the first query statement, and then replace the identified alias fields with corresponding original fields. The data sources in the first query statement are view tables, and the data sources in the first query statement need to be modified into the specified database in the original query statement.
Because the first query statement is a query statement for the data contained in the view chart, which is equivalent to the need to screen the target data based on the query condition in the original query statement and then screen the specified data from the target data based on the query condition in the first query statement, the screening condition in the original query statement needs to be added to the condition clause of the first query statement.
In specific implementation, SQL analysis is performed on the query sentence, query keywords such as SELECT, FROM, WHERE, GROUP BY, ORDER BY and the like are obtained, and a SELECT clause, a FROM clause, a WHERE clause, a GROUP BY clause, an ORDER BY clause and the like are divided based on the query keywords.
SQL parsing includes lexical analysis and grammar/semantic analysis. The lexical analysis mainly converts a query sentence into a plurality of Token, and the Token can be a Keyword (also called symbol) or a non-Keyword. For example, lexical analysis of the query statement "SELECT username FROM userinfo" may result in 4 tokens, where tokens belonging to a Keyword are SELECT and FROM. The grammar analysis is based on lexical analysis, and determines whether the word in the query sentence accords with grammar logic, for example, "SELECT foo+100FROM POKES" is a sentence which accords with grammar, and "SELECT foo+100FROM" is an illegal sentence, because a table name must follow FROM, and the grammar analyzer reports an error at this time. The lexical analysis and the grammatical analysis are the prior art and are not described in detail. After the lexical analysis and the grammar analysis, an abstract syntax tree as shown in fig. 4 can be obtained, the abstract syntax tree is a representation form of a tree structure of a query sentence, each node on the abstract syntax tree corresponds to a word, the structure of the abstract syntax tree reflects grammar, the abstract syntax tree is constructed along with the process of the grammar analysis, when the grammar analysis is normally finished, a grammar analyzer outputs an abstract syntax tree, the structural contents of the query sentence and the abstract syntax tree are in one-to-one correspondence, for example, a node SELECT corresponds to a keyword SELECT, a node Fields corresponds to a query field clause, the query field clause can be further divided into a plurality of nodes, such as username and ismale, a node FROM corresponds to a keyword FROM, a node Tables corresponds to a data source clause, the data source clause can be further divided into a node userinfo, a node WHERE corresponds to a keyword while, a node Conditions corresponds to a condition clause, and the condition clause can be further divided into a plurality of nodes.
In specific implementation, referring to fig. 5, the correspondence may be obtained by the following method:
s501, based on the query field segmenter, the query field clause is segmented into a plurality of query fields.
Specifically, the subtree parts of node Fields in the abstract syntax tree are obtained, and the subtree parts of node Fields are merged to obtain the query field clause.
The query field segmenter is a character used for segmenting each query field in the query field clause, and is generally comma. For example, in "SELECT CREATE _user AS count_create_user, api_name, page_id", the query fields "create_user AS count_create_user" and "api_name" are separated by commas.
In practice, the SELECT clause often includes functions that include the same symbols AS the query field delimiters, such AS "date_format (create_time, '%y-%m-%d') AS create_date", which typically appear in brackets. Therefore, before dividing the query field clause into a plurality of query fields, the symbols in brackets in the query field clause, which are identical to the query field separator, need to be replaced by preset special characters, then dividing the query field after replacing the symbols into a plurality of query fields based on the query field separator, and finally, replacing the special characters in the plurality of query fields with the query field separator, thus the method can be used for mistracking.
S502, acquiring an original field and an alias field with a corresponding relation from any query field aiming at any query field containing an alias definition character, wherein the alias definition character is used for defining the character with the corresponding relation between the original field and the alias field.
Wherein the alias definition characters are determined by SQL statements, and the alias definition characters may be AS, CASE or space characters. The alias-defining characters contained in the query field may be identified by way of regular matching.
In specific implementation, step S502 specifically includes the following steps:
If the alias definition character contained in any query field is AS, determining a field before the AS in any query field AS an original field, and determining a field after the AS in any query field AS an alias field corresponding to the original field;
if the alias definition character contained in any query field is CASE, determining a field between CASE and END in any query field as an original field, and determining a field after END in any query field as an alias field corresponding to the original field;
if any query field does not contain AS and CASE and any query field contains a space character, determining a field before the space character in any query field AS an original field and determining a field after the space character in any query field AS an alias field corresponding to the original field.
For example, the original field in the query field "Sum (c+2) AS C" is "Sum (c+2)", and the alias field is "C". The original field in the query field "CASE WHEN substr (E, 9, 2) < '06' th ' night ' WHEN substr (E, 9, 2) < '10' th ' breakfast ' WHEN substr (E, 9, 2) < '15' th ' lunch ' WHEN substr (E, 9, 2) < '20' th ' dinner ' ELSE ' night ' AS E" is "WHEN substr (E, 9, 2) < '06' th ' night ' WHEN substr (E, 9, 2) < '10' th ' breakfast ' WHEN substr (E, 9, 2) < '15' th ' lunch ' WHEN substr (E, 9, 2) < '20' th ' ELSE ' night '" and the alias field is "E". Between B and B in query field "B B" is a space character, and thus the original field is "B" and the alias field is "B".
On the basis of any one of the above embodiments, the data query method of the embodiment of the present application further includes the steps of marking the alias field as a field incapable of using the aggregation function if the original field corresponding to the alias field has used the aggregation function, and outputting a prompt message incapable of using the aggregation function when the aggregation function is used for the field marked as incapable of using the aggregation function.
For example, in "Sum (c+2) AS C", the aggregation function Sum is already used for the original field Sum (c+2) corresponding to the alias field C, so C is actually a value, and the aggregation function cannot be used for the value, and therefore, the data analysis client marks the alias field C AS a field where the aggregation function cannot be used. When the user wants to add the alias field c to the numeric bar, a prompt message that the alias field cannot use the aggregation function is generated, and errors caused by secondary aggregation are prevented.
On the basis of any of the above embodiments, the query condition in the original query statement may be added to the condition clause of the first query statement by adding the query condition in the original query statement before the query condition in the condition clause of the first query statement.
For example, the query condition in the original query statement is A >10and B <1, the condition clause of the first query statement is WHERE C >2, and the condition clause of the second query statement is WHERE A >10and B <1and C >2. Thus, the query conditions in the original query statement are executed preferentially, and then the query conditions in the first query statement are executed, so that the index hit rate is improved.
On the basis of any embodiment, the user can also add placeholders representing the custom conditions in the condition clauses of the original query statement, so that the subsequent user can conveniently customize the query conditions. Referring to fig. 3C, a user may input a custom query condition through a custom condition input box in a condition field.
Based on the above, the method of the embodiment of the application further comprises the following steps of deleting the custom query condition in the first query statement and replacing the placeholder added into the first query statement with the custom query condition if the first query statement contains the custom query condition.
For example, the condition clause of the original query statement is WHERE A >100and D { D }, WHERE D { D } is the placeholder for the custom condition, and when the user inputs a screening condition named D, e.g., D >100, then D { D } is replaced with D >100. For example, the condition input by the user is that WHERE C >2and D >100, the query condition of the original query sentence is added into the first query sentence, the obtained condition clause is that WHERE A >100and D{D}and C>2and D>100, D >100 in the condition sentence is deleted, D { D } is replaced by D >100 as a whole, and the finally obtained condition clause of the second query sentence is that WHERE A >100and D>100and C>2. If the user does not input the custom query condition, the data analysis client replaces D { D } with 1=1 as a whole, and the server does not affect the query result and the query performance after executing 1=1 because the condition 1=1 is wire.
In practical application, a primary view chart is generated based on an original query statement Q0 input by a user, the original query statement Q0 is used for indicating that data is queried from a specified database, and the user can also perform query operation on the primary view chart, at this time, the data analysis client generates a new secondary view chart based on the query statement Q1 input on the primary view chart. When a user performs a query operation on the secondary view table, the data analysis client acquires a query statement Q2 for indicating that the specified data is queried from the secondary view table, modifies the query statement Q2 based on the query statement Q1 to acquire a query statement Q '2 for indicating that the specified data is queried from the primary view table, then modifies the query statement Q'2 based on the original query statement Q0 to acquire a query statement Q '2 for indicating that the specified data is queried from the specified database, and acquires the specified data from the specified database based on the query statement Q'2.
The method for modifying the query term Q2 based on the query term Q1 may refer to a specific implementation manner of modifying the first query term based on the original query term provided in the embodiment of the present application, for example, replacing the alias field in the query term Q2 with the corresponding original field according to the correspondence between the original field and the alias field obtained from the query term Q1, replacing the view table in the query term Q2 with the data source (i.e., the primary view table) in the query term Q1, and adding the query condition in the query term Q1 to the condition clause of the query term Q2. Wherein, the correspondence between the original field and the alias field is characterized by being obtained from the query statement Q1 in the manner shown in fig. 5.
The specific implementation manner of modifying the query statement Q'2 based on the original query statement Q0 may refer to the specific implementation manner of modifying the first query statement based on the original query statement provided in the embodiment of the present application, which is not described herein.
Of course, the user may also build a three-level view chart based on the two-level view table, a four-level view chart based on the three-level view chart, and so on. When data is queried, when a query statement QN for indicating to query the designated data from the N-level view chart is obtained, the query statement QN is modified based on the query statement QN-1 corresponding to the N-1 level view chart to obtain a query statement Q 'N for indicating to query the designated data from the N-1 level view chart, the query statement Q 'N is modified based on the query statement QN-2 corresponding to the N-2 level view chart to obtain a query statement Q 'N for indicating to query the designated data from the N-2 level view chart, and so on, the query statement aiming at the N-level view chart can be finally converted into the query statement for directly querying the data from the designated data, namely the query statement without sub-query, by means of recursively optimizing the query statement step by step, so that the query efficiency is improved, and the query performance is optimized.
As shown in fig. 6, based on the same inventive concept as the above-mentioned data query method, the embodiment of the present application further provides a data query device 60, which includes an acquisition module 601, a modification module 602, and a query module 603.
An obtaining module 601, configured to obtain a first query statement for indicating to query specified data from a view chart, where the view chart is generated based on an original query statement, and the original query statement is used to indicate to query the specified data from a specified database;
A modifying module 602, configured to modify the first query statement based on the original query statement, to obtain a second query statement for indicating that the specified data is queried from the specified database;
A query module 603, configured to obtain the specified data based on the second query statement.
Optionally, the modification module 602 is specifically configured to replace an alias field in the first query statement with a corresponding original field according to a correspondence between the original field and the alias field obtained from the original query statement, replace a view table in the first query statement with a specified database, and add a query condition in the original query statement to a condition clause of the first query statement.
Optionally, the data query device 60 further includes a parsing module 604, configured to obtain the correspondence relationship by:
Splitting the query field clause into a plurality of query fields based on the query field segmenter;
And acquiring an original field and an alias field with a corresponding relation from any query field aiming at any query field containing the alias definition characters, wherein the alias definition characters are used for defining the characters with the corresponding relation between the original field and the alias field.
Optionally, the parsing module 604 is specifically configured to:
If the alias definition character contained in any query field is AS, determining a field before the AS in any query field AS an original field, and determining a field after the AS in any query field AS an alias field corresponding to the original field;
if the alias definition character contained in any query field is CASE, determining a field between CASE and END in any query field as an original field, and determining a field after END in any query field as an alias field corresponding to the original field;
if any query field does not contain AS and CASE and any query field contains a space character, determining a field before the space character in any query field AS an original field and determining a field after the space character in any query field AS an alias field corresponding to the original field.
Optionally, the parsing module 604 is specifically configured to:
replacing symbols in brackets in the inquiry field clause, which are the same as symbols in the inquiry field separator, with preset special characters;
Dividing the query field after replacing the symbol into a plurality of query fields based on the query field divider;
the special characters in the plurality of query fields are replaced with query field segmenters.
Optionally, the data query device 60 further includes a marking module for:
if the original field corresponding to the alias field uses the aggregation function, marking the alias field as a field which cannot use the aggregation function;
When the aggregation function is used for the field marked as the aggregation function which cannot be used, the prompt information of the aggregation function which cannot be used is output.
Optionally, the modification module 602 is specifically configured to add the query condition in the original query statement before the query condition in the condition clause of the first query statement.
Optionally, when the condition clause of the original query statement includes a placeholder corresponding to the custom condition, the modification module 602 is further configured to delete the custom query condition in the first query statement and replace the placeholder added to the first query statement with the custom query condition if it is determined that the first query statement includes the custom query condition.
The data query device and the data query method provided by the embodiment of the application adopt the same inventive concept, can obtain the same beneficial effects, and are not described in detail herein.
Based on the same inventive concept as the data query method, the embodiment of the application also provides an electronic device, which can be a desktop computer, a portable computer, a smart phone, a tablet computer, a Personal digital assistant (Personal DIGITAL ASSISTANT, PDA), a server and the like. As shown in fig. 7, the electronic device 70 may include a processor 701 and a memory 702.
The Processor 701 may be a general purpose Processor such as a Central Processing Unit (CPU), digital signal Processor (DIGITAL SIGNAL Processor, DSP), application SPECIFIC INTEGRATED Circuit (ASIC), field programmable gate array (Field Programmable GATE ARRAY, FPGA) or other programmable logic device, discrete gate or transistor logic, discrete hardware components, or may implement or perform the methods, steps, and logic blocks disclosed in embodiments of the application. The general purpose processor may be a microprocessor or any conventional processor or the like. The steps of a method disclosed in connection with the embodiments of the present application may be embodied directly in a hardware processor for execution, or in a combination of hardware and software modules in the processor for execution.
The memory 702 is a non-volatile computer-readable storage medium that can be used to store non-volatile software programs, non-volatile computer-executable programs, and modules. The Memory may include at least one type of storage medium, which may include, for example, flash Memory, hard disk, multimedia card, card Memory, random access Memory (Random Access Memory, RAM), static random access Memory (Static Random Access Memory, SRAM), programmable Read-Only Memory (Programmable Read Only Memory, PROM), read-Only Memory (ROM), charged erasable programmable Read-Only Memory (ELECTRICALLY ERASABLE PROGRAMMABLE READ-Only Memory, EEPROM), magnetic Memory, magnetic disk, optical disk, and the like. The memory is any other medium that can be used to carry or store desired program code in the form of instructions or data structures and that can be accessed by a computer, but is not limited to such. The memory 702 in embodiments of the present application may also be circuitry or any other device capable of performing storage functions for storing program instructions and/or data.
An embodiment of the present application provides a computer-readable storage medium storing computer program instructions for use with the above-described electronic device, which contains a program for executing the above-described data query method.
The computer storage media described above can be any available media or data storage device that can be accessed by a computer, including, but not limited to, magnetic storage (e.g., floppy disks, hard disks, magnetic tape, magneto-optical disks (MOs), etc.), optical storage (e.g., CD, DVD, BD, HVD, etc.), and semiconductor storage (e.g., ROM, EPROM, EEPROM, non-volatile storage (NAND FLASH), solid State Disk (SSD)), etc.
The foregoing embodiments are only used for describing the technical scheme of the present application in detail, but the descriptions of the foregoing embodiments are only used for helping to understand the method of the embodiments of the present application, and should not be construed as limiting the embodiments of the present application. Variations or alternatives readily apparent to those skilled in the art are intended to be encompassed within the scope of the embodiments of the present application.