CN116340398B - Method and system for deriving online custom report query - Google Patents
Method and system for deriving online custom report query Download PDFInfo
- Publication number
- CN116340398B CN116340398B CN202310600882.4A CN202310600882A CN116340398B CN 116340398 B CN116340398 B CN 116340398B CN 202310600882 A CN202310600882 A CN 202310600882A CN 116340398 B CN116340398 B CN 116340398B
- Authority
- CN
- China
- Prior art keywords
- data
- report
- query
- file
- screening
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Active
Links
- 238000000034 method Methods 0.000 title claims abstract description 57
- 238000012216 screening Methods 0.000 claims abstract description 58
- 238000012545 processing Methods 0.000 claims abstract description 44
- 238000013500 data storage Methods 0.000 claims abstract description 27
- 230000001174 ascending effect Effects 0.000 claims description 5
- 239000003086 colorant Substances 0.000 claims description 3
- 230000002452 interceptive effect Effects 0.000 claims description 3
- 230000011218 segmentation Effects 0.000 claims description 3
- 238000007405 data analysis Methods 0.000 abstract description 6
- 230000008569 process Effects 0.000 description 8
- 238000009795 derivation Methods 0.000 description 5
- 230000007246 mechanism Effects 0.000 description 4
- 239000000284 extract Substances 0.000 description 3
- 230000006870 function Effects 0.000 description 3
- 238000004458 analytical method Methods 0.000 description 2
- 230000009286 beneficial effect Effects 0.000 description 2
- 238000013075 data extraction Methods 0.000 description 2
- 238000013461 design Methods 0.000 description 2
- 238000010586 diagram Methods 0.000 description 2
- 230000008676 import Effects 0.000 description 2
- 238000002347 injection Methods 0.000 description 2
- 239000007924 injection Substances 0.000 description 2
- 238000012423 maintenance Methods 0.000 description 2
- 238000013508 migration Methods 0.000 description 2
- 230000005012 migration Effects 0.000 description 2
- 238000012163 sequencing technique Methods 0.000 description 2
- 230000002159 abnormal effect Effects 0.000 description 1
- 238000013475 authorization Methods 0.000 description 1
- 230000005540 biological transmission Effects 0.000 description 1
- 238000004422 calculation algorithm Methods 0.000 description 1
- 238000004364 calculation method Methods 0.000 description 1
- 238000013079 data visualisation Methods 0.000 description 1
- 238000011161 development Methods 0.000 description 1
- 230000018109 developmental process Effects 0.000 description 1
- 230000000694 effects Effects 0.000 description 1
- 238000005516 engineering process Methods 0.000 description 1
- 238000000605 extraction Methods 0.000 description 1
- 238000001914 filtration Methods 0.000 description 1
- 238000009434 installation Methods 0.000 description 1
- 238000007726 management method Methods 0.000 description 1
- 238000012986 modification Methods 0.000 description 1
- 230000004048 modification Effects 0.000 description 1
- 238000011084 recovery Methods 0.000 description 1
- 239000000243 solution Substances 0.000 description 1
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/248—Presentation of query results
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F40/00—Handling natural language data
- G06F40/10—Text processing
- G06F40/166—Editing, e.g. inserting or deleting
- G06F40/186—Templates
-
- Y—GENERAL TAGGING OF NEW TECHNOLOGICAL DEVELOPMENTS; GENERAL TAGGING OF CROSS-SECTIONAL TECHNOLOGIES SPANNING OVER SEVERAL SECTIONS OF THE IPC; TECHNICAL SUBJECTS COVERED BY FORMER USPC CROSS-REFERENCE ART COLLECTIONS [XRACs] AND DIGESTS
- Y02—TECHNOLOGIES OR APPLICATIONS FOR MITIGATION OR ADAPTATION AGAINST CLIMATE CHANGE
- Y02D—CLIMATE CHANGE MITIGATION TECHNOLOGIES IN INFORMATION AND COMMUNICATION TECHNOLOGIES [ICT], I.E. INFORMATION AND COMMUNICATION TECHNOLOGIES AIMING AT THE REDUCTION OF THEIR OWN ENERGY USE
- Y02D10/00—Energy efficient computing, e.g. low power processors, power management or thermal management
Abstract
The application belongs to the technical field of computers, and particularly relates to a method and a system for inquiring and exporting an online custom report. The method comprises the following steps: receiving a data query request sent by a user side, and extracting query data matched with the data query request from a database; acquiring a data selection instruction of a front-end interface of a user side, and screening out required data from query data as report data through screening conditions; the method comprises the steps that keyword retrieval is carried out according to report data, a report template is configured according to report formats and contents customized through a front-end interface, and when a received report generation request is received, a background data processing server writes the report data into the report template according to an importing rule so as to generate a report file; the background data processing server stores the generated report file into the data storage server so as to enable the user side to download the report file. The application can meet the personalized requirements of different users, improve the efficiency and accuracy of data analysis and report query, and reduce the cost of users.
Description
Technical Field
The application belongs to the technical field of computers, and particularly relates to a method and a system for inquiring and exporting an online custom report.
Background
In modern information-based society, data analysis and report queries have become important tools for enterprises and organizations to make management decisions. However, the conventional data query method often needs to manually derive a table format such as Excel or CSV for data processing, which is inefficient and prone to error. Some report inquiry and export tools and systems exist at present, and some business software provides report designers and report inquiry tools based on a graphical interface, so that report design and inquiry can be conveniently realized. However, these business software generally require additional authorization fees and are relatively fixed in functionality, and are difficult to customize according to the individual needs of the user.
In addition, some open-source data visualization tools and frameworks such as Tableau, power BI, D3.Js and the like also provide more flexible and powerful report query and export functions, and can be customized and developed according to the requirements of users. However, these tools require specialized data analysts for development and maintenance, are high in threshold, and can be difficult for some small businesses and organizations to afford corresponding costs.
Disclosure of Invention
The present application has been made in view of the above-described problems. The application provides a simple, easy-to-use, flexible and accurate online custom report query deriving method and system, which can meet the personalized requirements of different users, improve the efficiency and accuracy of data analysis and report query and reduce the cost of users.
The application is realized by adopting the following technical scheme:
in a first aspect, the present application provides a method for deriving an online custom report query, where the method includes:
receiving a data query request sent by a user side, and extracting query data matched with the data query request from a database;
acquiring a data selection instruction of a front-end interface of a user side, and screening out required data from the query data as report data through screening conditions;
the method comprises the steps that keyword retrieval is carried out according to report data, a report template is configured according to report formats and contents customized through a front-end interface, and when a received report generation request is received, a background data processing server writes the report data into the report template according to an importing rule so as to generate a report file;
and the background data processing server stores the generated report file into a data storage server so as to enable the user side to download the report file.
As a further scheme of the application, the received data query request sent by the user terminal comprises a query field, a query condition, a sorting mode and paging information, wherein the query field is a field of data to be queried, which is set through a front end interface of the user terminal, and the query field is a single field or a combination of a plurality of fields; the query condition is a conditional expression formed by combining query requirements through a logic operator and using SQL sentences, and the ordering mode is to order according to the ascending order or descending order of the number of the query fields; the paging information is the data volume and the current page number displayed by each page.
As a further aspect of the present application, extracting query data matching the data query request from a database includes the steps of:
1) Positioning a data table to be queried in a database according to the query field;
2) Screening out data conforming to the conditional expression from the data table according to the query condition, and screening by using a WHERE clause in the SQL statement;
3) Ordering the screened data BY using ORDER BY clauses in the SQL clauses according to the ordering mode;
4) And returning the data of the specified page number and the data quantity of each page according to the paging information, and completing the paging operation by using the LIMIT clause in the SQL sentence to obtain matched query data.
As a further scheme of the application, the acquired data selection instruction of the front-end interface of the user terminal comprises report types, data sources, data fields, screening conditions and a combination mode; when the required data is screened out from the query data as report data through screening conditions, the method comprises the following steps:
positioning a data table to be queried from a database according to a data source in the data selection instruction;
screening data to be queried from the data table according to the query field in the data selection instruction;
screening by using a WHERE clause in the SQL statement according to screening conditions in the data selection instruction to obtain screened data;
according to the combination mode in the data selection instruction, the screened data are ordered BY using the ORDER BY clause in the SQL statement, and are grouped BY using the GROUP BY clause in the SQL statement;
and exporting the screened data into a required report format to serve as report data.
When the application is used for keyword retrieval according to the report data, the index is established for all the fields to be retrieved in the report data, the keywords are used as index items, the data row corresponding to the fields containing the keywords is recorded, and the inverted index is used for keyword retrieval of the report data, and the steps are as follows:
traversing all fields to be searched in report data, taking keywords contained in each field as index items, and establishing an inverted index table;
when an inverted index table is established, a hash table data structure is adopted, data lines corresponding to each keyword are recorded, and the data lines are recorded as unique identifiers of the data lines, pointers or indexes pointing to the data lines;
when keyword retrieval is carried out on report data, searching a data row corresponding to the keyword in the inverted index table, and screening and sorting according to requirements to obtain retrieved keywords;
the built inverted index table is an index table based on a single field, an index table based on a plurality of fields or an index table based on word segmentation.
As a further scheme of the application, the report template configuration method comprises the following steps:
determining a basic format of a report by a user according to service requirements, wherein the basic format of the report comprises a title, a header, a column number and a line number of the report;
searching from a report template library according to the basic format of the report to obtain a search result, updating and typesetting the search result by using a table control or a custom style according to the designated column number and row number, and adjusting the cell size of the report template in the search result;
formatting and typesetting according to report content defined by a front-end interface, setting fonts, colors and alignment modes of the cells, adding charts and formula modes, and adjusting and optimizing a report template to obtain a configured report template.
As a further scheme of the application, when a report generation request is received, a background data processing server writes report data into a report template in a CSV (Comma Separated Values) text file format, and writes the report data into the report template in a CSV format, comprising the following steps:
determining the file name and the storage path of the CSV file;
creating a CSV file containing column names according to the column numbers and column names of the tables in the report template, and storing the CSV file under a specified path;
and screening out data meeting the requirements from the report data according to screening conditions in the data selection instruction, writing the data into a CSV file, and converting the CSV file into a report file.
In a second aspect, the present application also provides an online custom report query export method, which includes the following steps:
the user terminal selects the required data query condition and data selection instruction through the front-end interface;
the background data processing server extracts query data matched with the received data query request from the database according to the received data query request;
the background data processing server screens out the required data from the query data through screening conditions to serve as report data;
the background data processing server adopts an inverted index mode to search keywords of report data, and searches and acquires corresponding report data;
the background data processing server configures a report template according to a report format and content defined by a front-end interface;
when a report generation request is received, the background data processing server writes report data into a report template according to an importing rule to generate a report file;
the generated report files are stored in a data storage server, and the user side downloads the required report files through a front-end interface.
The application provides a system for deriving the online custom report query, which comprises a user terminal, a database server, a data query server, a background data processing server and a data storage server; the client communicates with the data query server, and is used for sending a report generation request to the background data processing server, wherein the background data processing server is used for storing the generated report file into the data storage server, and the client is used for downloading the required report file through the data storage server; wherein:
the database server is used for storing report data to be queried;
the data query server is used for receiving a data query request sent by a user side and extracting query data matched with the data query request from a database;
the user terminal receives the data query request and the report generation request of the user and sends the data query request and the report generation request to the background data processing server;
the background data processing server is used for generating report files according to the report data and the report templates and storing the report files into the data storage server;
the data storage server is used for storing the generated report file and providing a download server for the user side.
As a further scheme of the application, the user side is provided with a front-end interface, and the front-end interface is used for providing an interactive interface for a user to carry out data selection instructions and screening condition setting.
As a further aspect of the present application, the background data processing server further includes:
the request acquisition module is used for receiving a request sent by a user side;
the data extraction module is used for extracting query data matched with the data query request from a database;
the data screening module is used for screening the required data from the query data as report data through screening conditions according to the acquired data selection instruction of the front-end interface of the user terminal;
the template configuration module is used for carrying out keyword retrieval according to the report data and configuring a report template according to a report format and content customized through a front-end interface;
and the report generation module is used for writing report data into the report template according to the importing rules to generate a report file when a received report generation request is received, wherein the generated report file is stored in the data storage server so as to be downloaded by a user side.
The technical scheme provided by the application has the following beneficial effects:
the method and the system for deriving the online custom report query are suitable for enterprises and organizations needing data analysis and report query, such as a point exchange platform, a point mall platform and the like. Compared with the traditional data query mode, the application can realize the following steps:
compared with the prior art, the method and the system for deriving the online custom report query have the following beneficial effects:
1. the query efficiency is improved: the keyword retrieval is carried out by adopting the inverted index method, so that the query efficiency can be greatly improved, and meanwhile, the keywords can be screened and ordered according to the requirements to obtain the retrieved keywords.
2. Custom report format and content: through the front-end interface user-defined report format and content, the user can flexibly select the required data and report style, thereby meeting different requirements.
3. The report file is convenient to generate: the background data processing service writes the report data into the report template according to the importing rules to generate a report file, and a user can conveniently download and use the generated report file.
4. Improving the readability of the data: the report style and the content are customized according to the requirements of the user, and the readability and the understandability of the data can be improved, so that the user can be better helped to analyze and process the data.
5. Data security is improved: the generated report file is stored in the data storage service, so that the safety of the data can be ensured, and the user can conveniently perform data backup and migration.
In summary, the method and the system for deriving the online custom report query have the effects of improving the efficiency and the accuracy of data query and processing, flexibly customizing the report, reducing the cost of a user, improving the accuracy of the data and being widely applied; the user can also customize the report format and the content according to the own requirements, so that the flexibility and the accuracy of data analysis are greatly improved; the data storage server can effectively store the data queried and exported by the user, and is convenient for the user to carry out subsequent analysis and processing.
Drawings
The accompanying drawings are included to provide a further understanding of the application and are incorporated in and constitute a part of this specification, illustrate the application and together with the embodiments of the application, serve to explain the application. In the drawings:
FIG. 1 is a flow chart of a method for online custom report query export in accordance with one embodiment of the present application.
FIG. 2 is a flowchart of extracting query data in an online custom report query derivation method according to an embodiment of the present application.
FIG. 3 is a flowchart of screening report data in the method for deriving an online custom report query according to an embodiment of the present application.
FIG. 4 is a flowchart of keyword retrieval in an online custom report query derivation method according to an embodiment of the present application.
FIG. 5 is a flowchart of a report template configuration in a detected image in an online custom report query derivation method according to an embodiment of the present application.
FIG. 6 is a flowchart of writing report data in a detected image into a report template in an online custom report query derivation method according to an embodiment of the present application.
FIG. 7 is a system block diagram of an online custom report query export system in accordance with one embodiment of the application.
Description of the embodiments
The present application will be described in further detail with reference to the drawings and examples, in order to make the objects, technical solutions and advantages of the present application more apparent. It should be understood that the specific embodiments described herein are for purposes of illustration only and are not intended to limit the scope of the application.
It is noted that all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this application belongs; the terminology used in the description of the applications herein is for the purpose of describing particular embodiments only and is not intended to be limiting of the application; the terms "comprising" and "having" and any variations thereof in the description of the application and the claims and the description of the drawings above are intended to cover a non-exclusive inclusion.
The following description of at least one exemplary embodiment is merely exemplary in nature and is in no way intended to limit the disclosure, its application, or uses.
In the process of realizing the disclosure, the embodiment of the application provides an online custom report query and export method and system which support various aspects of the format, layout, data source, calculation logic and the like of the custom report, can meet the personalized requirements of different users, flexibly customize the report, can quickly and accurately query and export the required data, and improve the query efficiency.
The application adopts an online mode to provide the server, does not need to install any software, and can be operated by a user only through a browser, thereby avoiding the installation and maintenance costs of the software and reducing the cost of the user. And moreover, a strict authority control mechanism is adopted, so that the safety and accuracy of data are ensured, errors caused by manual operation are avoided, and the method is suitable for various types of point exchange platforms, point mall platforms and other enterprises and organizations needing data inquiry and report export, and is wide in application.
In particular, embodiments of the present application are further described below with reference to the accompanying drawings.
Referring now to FIG. 1, FIG. 1 is a flow chart illustrating a flow of one embodiment of an online custom report query derivation method according to the present disclosure. For convenience of explanation, only portions relevant to the embodiments of the present application are shown. In the embodiment of the application, the embodiment provides an online custom report query deriving method, which comprises the following steps:
s10, receiving a data query request sent by a user side, and extracting query data matched with the data query request from a database;
the received data query request sent by the user terminal comprises query fields, query conditions, a sorting mode and paging information, wherein the query fields are fields of data to be queried, which are set through a front end interface of the user terminal, and the query fields are single fields or a combination of a plurality of fields; the query condition is a conditional expression formed by combining query requirements through a logic operator and using SQL sentences, and the ordering mode is to order according to the ascending order or descending order of the number of the query fields; the paging information is the data volume and the current page number displayed by each page.
In this embodiment, referring to fig. 2, extracting query data matching the data query request from a database includes the following steps:
s101, positioning a data table to be queried in a database according to a query field;
s102, screening out data conforming to a conditional expression from the data table according to query conditions, and screening by using a WHERE clause in an SQL statement;
s103, sorting the screened data BY using ORDER BY clauses in the SQL statement according to a sorting mode;
and S104, returning the data of the designated page number and the data quantity of each page according to the paging information, and completing the paging operation by using the LIMIT clause in the SQL sentence to obtain matched query data.
In the process of extracting data, accuracy and safety of the data need to be ensured, and injection attack and other safety problems of malicious users are prevented. Therefore, when the functions of data query and extraction are realized, strict authority control mechanisms and countermeasure are required to be adopted, so that the safety and reliability of the data are ensured.
For example, the condition to be satisfied when the query condition is the data that the user needs to query may be a combination of a plurality of conditions, such as equal to, greater than, less than, including, etc., in the data query request. The multiple conditions are combined by logical operators (e.g., and, or, not, etc.), where the value of the specified field is equal to the specified value when the query condition is equal to. For example, "trade name equals 'handset'"; when the query condition is not equal, the value of the specified field is not equal to the specified value. For example, "commodity category is not equal to 'computer'"; when the query condition is greater than, the value of the specified field is greater than the specified value. For example, "commodity price is greater than 1000 yuan"; when the query condition is less than, the value of the specified field is less than the specified value. For example, "commodity price less than 500 yuan"; when the query condition is equal to or greater than the specified value, the value of the specified field is equal to or greater than the specified value. For example, "the number of commodities is 10 or more"; when the query condition is equal to or less, the value of the specified field is equal to or less than the specified value. For example, "commodity sales are 100 pieces or less"; the value of the specified field contains the specified string when the query condition is containing. For example, "trade name includes 'cell phone'".
S20, acquiring a data selection instruction of a front-end interface of the user side, and screening out required data from the query data as report data through screening conditions.
The acquired data selection instruction of the front-end interface of the user side comprises report types, data sources, data fields, screening conditions and a combination mode.
The report type, i.e. the report type that the user needs to generate, may be a table, a chart, a graphical report, etc. The data source, i.e. the data source that the user needs to query, may be a specific data table, database or other data storage means.
The data field, namely the data field which needs to be displayed in the report by the user, can be a single field or a combination of a plurality of fields; the filtering condition, i.e. the condition that the user needs to satisfy for the data to be filtered, may be a combination of a plurality of conditions, such as equal to, greater than, less than, including, etc.
The combination mode comprises a sequencing mode and a grouping mode, wherein the sequencing mode is that the data which a user needs to inquire is sequenced according to which field, and can be in ascending order or descending order; the grouping mode, i.e. which field the user needs to group, may be a single field or a combination of fields.
In this embodiment, referring to fig. 3, when screening out required data from the query data as report data by a screening condition, the method includes the following steps:
s201, positioning a data table to be queried from a database according to a data source in a data selection instruction;
s202, screening data to be queried from the data table according to a query field in a data selection instruction;
s203, screening by using a WHERE clause in the SQL statement according to screening conditions in the data selection instruction to obtain screened data;
s204, sorting the screened data BY using ORDER BY clauses in the SQL statement according to a combination mode in the data selection instruction, and grouping BY using GROUP BY clauses in the SQL statement;
and S205, exporting the screened data into a required report format to serve as report data.
When the screening condition is realized, the security and reliability of the data are considered, and injection attack and other security problems of malicious users are avoided. Therefore, when the screening condition is realized, a strict authority control mechanism and countermeasure are required to ensure the security and reliability of the data.
For example: the access control to the system resource can be realized only by authorized users, and the access and operation of the data can be realized by means of identity authentication, access Control List (ACL), role authority control and the like; the sensitive data is protected by adopting an encryption technology, and in the data transmission and storage process, the data is encrypted by adopting an encryption algorithm, so that the confidentiality and the integrity of the data are ensured, the data is prevented from being illegally acquired or tampered, the data is regularly backed up, the safety and the reliability of the backed-up data are ensured, and the data recovery capability when the system is abnormal is ensured.
S30, carrying out keyword retrieval according to the report data, configuring a report template according to the report format and content customized through a front-end interface, and writing the report data into the report template by a background data processing server according to an importing rule to generate a report file when a received report generation request is generated.
The user can customize the report format and the content, such as the header, the column names, the ordering and the like, according to the own requirements. When the keyword is searched according to the report data, establishing indexes for all fields to be searched in the report data, taking the keyword as an index item, and recording data rows corresponding to the fields containing the keyword.
In this embodiment, referring to fig. 4, keyword retrieval is performed on report data by using an inverted index, and the steps are as follows:
s301, traversing all fields to be retrieved in report data, and establishing an inverted index table by taking keywords contained in each field as index items;
s302, when an inverted index table is established, a hash table data structure is adopted, data lines corresponding to each keyword are recorded, and the data lines are recorded as unique identifiers of the data lines, pointers or indexes pointing to the data lines;
and S303, searching a data row corresponding to the keyword in the inverted index table when keyword retrieval is performed on the report data, and screening and sorting according to requirements to obtain the retrieved keyword.
The built inverted index table is an index table based on a single field, an index table based on a plurality of fields or an index table based on word segmentation.
In this embodiment, referring to fig. 5, the report template configuration includes the following steps:
s311, determining a basic format of a report by a user according to service requirements, wherein the basic format of the report comprises a title, a header, a column number and a line number of the report;
s312, searching from a report template library according to the basic format of the report to obtain a search result, updating and typesetting the search result by using a table control or a custom style according to the designated column number and row number, and adjusting the cell size of the report template in the search result;
s313, formatting and typesetting according to report content defined by a front-end interface, setting fonts, colors and alignment modes of the unit cells, adding charts and formula modes, and adjusting and optimizing the report template to obtain the configured report template.
In addition, the report template can be further adjusted and optimized, such as adding header and footer, setting page number, adding background color, etc. Through the steps, the report form template can be configured according to the report form format and the content of the front-end interface, and the keyword search result is presented according to the appointed format, so that a user can intuitively and conveniently know the required information.
In this embodiment, when a report generation request is received, the background data processing server writes report data into the report template in CSV (Comma Separated Values) text file format, as shown in fig. 6, and writes report data into the report template in CSV format, including the following steps:
s321, determining the file name and the storage path of the CSV file;
s322, creating a CSV file containing column names according to the column numbers and the column names of the tables in the report template, and storing the CSV file under a specified path;
for example, for a table containing three columns (name, age, gender), the following CSV file may be created:
name, age, sex
Zhang Mou, 20, man
Li Mou, 25, female
Wang Mou, 30, male.
And S323, screening out data meeting the requirements from the report data according to screening conditions in the data selection instruction, writing the data into a CSV file, and converting the CSV file into the report file.
For example, if all people with ages greater than or equal to 25 years old need to be screened from report data, the following codes may be used:
import csv
# open CSV file and write header
with open('report.csv', 'w', newline='') as csvfile:
writer = csv.writer(csvfile)
writer.writerow ([ ' name ', ' age ', ' sex ]
Screening data meeting conditions from report data and writing the data into CSV file
with open('report.csv', 'a', newline='') as csvfile:
writer = csv.writer(csvfile)
for data in report_data:
if data [ 'age' ] ] =25:
writer.writerow ([ data [ 'name' ], data [ 'age' ], data [ 'gender' ] ])
When converting the CSV file into the report file, an Excel tool may be used to open the CSV file and save the CSV file in an Excel file format, or a Python programming language may be used to convert the CSV file into a report file in another format, for example PDF, HTML, XML.
And S40, the background data processing server stores the generated report file into a data storage server so that the report file can be downloaded by the user side.
The online custom report query export method improves the efficiency and accuracy of report query and export. The user can acquire the required data and report information through the self-defined query conditions and report format, so that the complicated manual query and screening process is saved, and the query and export efficiency and accuracy are improved; and the data analysis and decision of the user are facilitated. A user can generate a report meeting the self requirements through a self-defined report format and content, and the analysis and decision making process of data are facilitated.
The application also provides an online custom report query exporting method, which comprises the following steps:
step 1, a user side selects required data query conditions and data selection instructions through a front-end interface;
step 2, the background data processing server extracts query data matched with the received data query request from the database according to the received data query request;
step 3, the background data processing server screens out the required data from the query data as report data through screening conditions;
step 4, the background data processing server adopts an inverted index mode to search keywords of the report data, and searches and acquires the corresponding report data;
step 5, the background data processing server configures a report template according to the report format and the content defined by the front-end interface;
step 6, when a report generation request is received, the background data processing server writes report data into a report template according to an importing rule to generate a report file;
and 7, storing the generated report file into a data storage server, and downloading the required report file by the user side through a front-end interface.
The online custom report query export method improves the expandability and flexibility of the system. The method adopts modularized design concept and flexible front-end interface, so that the system can conveniently perform function expansion and upgrading, and is suitable for different service demands; data security and privacy protection are enhanced. The method adopts a strict authority control mechanism and countermeasure, ensures the safety and reliability of the data, and avoids the risks of malicious use and leakage of sensitive data.
As shown in fig. 7, fig. 7 is a structural block diagram of an online custom report query export system provided by an embodiment of the present application, where the online custom report query export system is capable of executing the method for online custom report query export in any of the method embodiments described above. Specifically, in one embodiment of the present application, an online custom report query export system is provided, which includes a user terminal 100, a database server 400, a data query server 300, a background data processing server 200, and a data storage server 500; the client 100 communicates with the data query server 300, and is used for sending a report generation request to the background data processing server 200, where the background data processing server 200 is used for storing the generated report file into the data storage server 500, and the client 100 is used for downloading the required report file through the data storage server 500.
In this embodiment, the database server 400 is configured to store report data to be queried;
the data query server 300 is configured to receive a data query request sent by the user terminal 100, and extract query data matched with the data query request from a database;
the client 100 receives a data query request and a report generation request of a user, and sends the data query request and the report generation request to the background data processing server 200;
the background data processing server 200 is configured to generate a report file according to the report data and the report template, and store the report file in the data storage server 500;
the data storage server 500 is configured to store the generated report file, and provide a download server to the client 100.
The user terminal 100 is provided with a front-end interface 101, where the front-end interface 101 is used for providing an interactive interface for a user to perform data selection instructions and screening condition settings.
In this embodiment, the background data processing server 200 further includes:
a request acquisition module 201, configured to receive a request sent by the user terminal 100;
a data extraction module 202, configured to extract query data matching the data query request from a database;
the data screening module 204 is configured to screen, according to the acquired data selection instruction of the front end interface 101 of the user terminal 100, the required data from the query data through a screening condition, as report data;
the template configuration module 203 is configured to perform keyword retrieval according to the report data, and configure a report template according to the report format and content customized through the front-end interface 101;
the report generating module 205 is configured to write report data into a report template according to an import rule to generate a report file when receiving a report generating request, where the generated report file is stored in the data storage server 500, so that the user terminal 100 downloads the report file.
In this embodiment, the online custom report query export system may further employ the steps of the online custom report query export method described above, and may be applied to various types of point exchange platforms, point mall platforms, and other enterprises and organizations that need to perform data query and report export.
The online custom report query export system adopts the steps of the online custom report query export method when executing. Therefore, the operation process of the online custom report query and export system in this embodiment will not be described in detail.
In summary, the application improves the query efficiency, and the keyword retrieval is performed by adopting the inverted index method, so that the query efficiency can be greatly improved, and meanwhile, the keywords can be screened and sequenced according to the requirements to obtain the retrieved keywords. The report format and the content can be customized, and the user can flexibly select the required data and report style by customizing the report format and the content through the front-end interface, so that different requirements are met. The report file is convenient to generate, the background data processing service writes the report data into the report template according to the importing rule to generate the report file, and a user can conveniently download and use the generated report file. The readability of the data is improved, the report style and the content are customized according to the requirements of the user, and the readability and the understandability of the data can be improved, so that the user can be better helped to analyze and process the data. The data security is improved, the generated report file is stored in the data storage service, the data security can be ensured, and the user can conveniently carry out data backup and migration.
The foregoing description of the preferred embodiments of the application is not intended to be limiting, but rather is intended to cover all modifications, equivalents, and alternatives falling within the spirit and principles of the application.
Claims (5)
1. The method for deriving the online custom report query is characterized by comprising the following steps:
receiving a data query request sent by a user side, and extracting query data matched with the data query request from a database;
acquiring a data selection instruction of a front-end interface of a user side, and screening out required data from the query data as report data through screening conditions;
the method comprises the steps that keyword retrieval is carried out according to report data, a report template is configured according to report formats and contents customized through a front-end interface, and when a received report generation request is received, a background data processing server writes the report data into the report template according to an importing rule so as to generate a report file;
the background data processing server stores the generated report file into a data storage server so as to enable the user side to download the report file;
the received data query request sent by the user terminal comprises query fields, query conditions, a sorting mode and paging information, wherein the query fields are fields of data to be queried, which are set through a front end interface of the user terminal, and the query fields are single fields or a combination of a plurality of fields; the query condition is a conditional expression formed by combining query requirements through a logic operator and using SQL sentences, and the ordering mode is to order according to the ascending order or descending order of the number of the query fields; the paging information is the data quantity and the current page number displayed by each page;
extracting query data matching the data query request from a database, comprising the steps of:
positioning a data table to be queried in a database according to the query field;
screening out data conforming to the conditional expression from the data table according to the query condition, and screening by using a WHERE clause in the SQL statement;
according to the sorting mode, sorting the screened data by using an ORDERBY clause in the SQL sentence;
according to the paging information, returning the data of the designated page number and the data quantity of each page, and completing the paging operation by using the LIMIT clause in the SQL sentence to obtain matched query data;
when a received report generation request is received, the background data processing server writes report data into a report template in a CSV text file format, and writes the report data into the report template in the CSV format, and the method comprises the following steps:
determining the file name and the storage path of the CSV file;
creating a CSV file containing column names according to the column numbers and column names of the tables in the report template, and storing the CSV file under a specified path;
and screening out data meeting the requirements from the report data according to screening conditions in the data selection instruction, writing the data into a CSV file, and converting the CSV file into a report file.
2. The method for deriving an online custom report query as claimed in claim 1, wherein the obtained data selection instruction of the front-end interface of the user terminal includes report type, data source, data field, screening condition and combination mode; when the required data is screened out from the query data as report data through screening conditions, the method comprises the following steps:
positioning a data table to be queried from a database according to a data source in the data selection instruction;
screening data to be queried from the data table according to the query field in the data selection instruction;
screening by using a WHERE clause in the SQL statement according to screening conditions in the data selection instruction to obtain screened data;
according to the combination mode in the data selection instruction, ordering the screened data by using an ORDERBY clause in the SQL statement, and grouping by using a GROUPBY clause in the SQL statement;
and exporting the screened data into a required report format to serve as report data.
3. The method for deriving the online custom report query as claimed in claim 1, wherein when the keyword search is performed according to the report data, the method comprises the steps of establishing an index for all the fields to be searched in the report data, using the keyword as an index item, recording a data row corresponding to the field containing the keyword, and performing the keyword search for the report data by adopting an inverted index, wherein the steps are as follows:
traversing all fields to be searched in report data, taking keywords contained in each field as index items, and establishing an inverted index table;
when an inverted index table is established, a hash table data structure is adopted, data lines corresponding to each keyword are recorded, and the data lines are recorded as unique identifiers of the data lines, pointers or indexes pointing to the data lines;
when keyword retrieval is carried out on report data, searching a data row corresponding to the keyword in the inverted index table, and screening and sorting according to requirements to obtain retrieved keywords;
the built inverted index table is an index table based on a single field, an index table based on a plurality of fields or an index table based on word segmentation.
4. The method for deriving an online custom report query as claimed in claim 3, wherein configuring the report template comprises the steps of:
determining a basic format of a report by a user according to service requirements, wherein the basic format of the report comprises a title, a header, a column number and a line number of the report;
searching from a report template library according to the basic format of the report to obtain a search result, updating and typesetting the search result by using a table control or a custom style according to the designated column number and row number, and adjusting the cell size of the report template in the search result;
formatting and typesetting according to report content defined by a front-end interface, setting fonts, colors and alignment modes of the cells, adding charts and formula modes, and adjusting and optimizing a report template to obtain a configured report template.
5. An online custom report query deriving system, which is characterized by being built based on the online custom report query deriving method according to any one of claims 1-4; the online custom report query and export system comprises a user side, a database server, a data query server, a background data processing server and a data storage server; the client communicates with the data query server, and is used for sending a report generation request to the background data processing server, wherein the background data processing server is used for storing the generated report file into the data storage server, and the client is used for downloading the required report file through the data storage server; wherein:
the database server is used for storing report data to be queried;
the data query server is used for receiving a data query request sent by a user side and extracting query data matched with the data query request from a database, wherein the received data query request sent by the user side comprises query fields, query conditions, a sorting mode and paging information, the query fields are fields of data to be queried, which are set through a front end interface of the user side, and the query fields are single fields or a combination of a plurality of fields; the query condition is a conditional expression formed by combining query requirements through a logic operator and using SQL sentences, and the ordering mode is to order according to the ascending order or descending order of the number of the query fields; the paging information is the data quantity and the current page number displayed by each page;
wherein extracting query data matching the data query request from a database comprises:
positioning a data table to be queried in a database according to the query field;
screening out data conforming to the conditional expression from the data table according to the query condition, and screening by using a WHERE clause in the SQL statement;
according to the sorting mode, sorting the screened data by using an ORDERBY clause in the SQL sentence;
according to the paging information, returning the data of the designated page number and the data quantity of each page, and completing the paging operation by using the LIMIT clause in the SQL sentence to obtain matched query data;
the user terminal receives the data query request and the report generation request of the user and sends the data query request and the report generation request to the background data processing server, and the user terminal is provided with a front-end interface which is used for providing the user with an interactive interface for carrying out data selection instructions and screening condition setting;
when a received report generation request is received, the background data processing server writes report data into a report template in a CSV text file format, and writes the report data into the report template in the CSV format, and the method comprises the following steps:
determining the file name and the storage path of the CSV file;
creating a CSV file containing column names according to the column numbers and column names of the tables in the report template, and storing the CSV file under a specified path;
screening out data meeting the requirements from the report data according to screening conditions in the data selection instruction, writing the data into a CSV file, and converting the CSV file into a report file;
the background data processing server is used for generating report files according to the report data and the report templates and storing the report files into the data storage server;
the data storage server is used for storing the generated report file and providing a download server for the user side.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202310600882.4A CN116340398B (en) | 2023-05-25 | 2023-05-25 | Method and system for deriving online custom report query |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202310600882.4A CN116340398B (en) | 2023-05-25 | 2023-05-25 | Method and system for deriving online custom report query |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN116340398A CN116340398A (en) | 2023-06-27 |
| CN116340398B true CN116340398B (en) | 2023-08-18 |
Family
ID=86891526
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202310600882.4A Active CN116340398B (en) | 2023-05-25 | 2023-05-25 | Method and system for deriving online custom report query |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN116340398B (en) |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102567539A (en) * | 2011-12-31 | 2012-07-11 | 北京新媒传信科技有限公司 | Intelligent WEB report implementation method and intelligent WEB report implementation system |
| CN108268525A (en) * | 2016-12-30 | 2018-07-10 | 深圳市优朋普乐传媒发展有限公司 | A kind of report implementation method and device based on Excel template |
| CN111563368A (en) * | 2020-04-03 | 2020-08-21 | 江苏苏宁物流有限公司 | Report generation method and device, computer equipment and storage medium |
| CN111881192A (en) * | 2020-08-03 | 2020-11-03 | 浪潮云信息技术股份公司 | Method and system for generating visual configuration report, electronic equipment and storage medium |
| CN115061721A (en) * | 2022-07-12 | 2022-09-16 | 平安科技（深圳）有限公司 | Report generation method and device, computer equipment and storage medium |
-
2023
- 2023-05-25 CN CN202310600882.4A patent/CN116340398B/en active Active
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN102567539A (en) * | 2011-12-31 | 2012-07-11 | 北京新媒传信科技有限公司 | Intelligent WEB report implementation method and intelligent WEB report implementation system |
| CN108268525A (en) * | 2016-12-30 | 2018-07-10 | 深圳市优朋普乐传媒发展有限公司 | A kind of report implementation method and device based on Excel template |
| CN111563368A (en) * | 2020-04-03 | 2020-08-21 | 江苏苏宁物流有限公司 | Report generation method and device, computer equipment and storage medium |
| CN111881192A (en) * | 2020-08-03 | 2020-11-03 | 浪潮云信息技术股份公司 | Method and system for generating visual configuration report, electronic equipment and storage medium |
| CN115061721A (en) * | 2022-07-12 | 2022-09-16 | 平安科技（深圳）有限公司 | Report generation method and device, computer equipment and storage medium |
Also Published As
| Publication number | Publication date |
|---|---|
| CN116340398A (en) | 2023-06-27 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN113342821B (en) | Report configuration method, device, equipment and computer storage medium | |
| CN108932294B (en) | Resume data processing method, device, equipment and storage medium based on index | |
| EP3855324A1 (en) | Associative recommendation method and apparatus, computer device, and storage medium | |
| CN1713179B (en) | Impact analysis in an object model | |
| CN102999561B (en) | The context trend of data set and data, services | |
| US8250651B2 (en) | Identifying attributes of aggregated data | |
| CA2473446A1 (en) | Identifier vocabulary data access method and system | |
| CN105550241A (en) | Multidimensional database query method and apparatus | |
| CN109241384B (en) | Scientific research information visualization method and device | |
| CN104750776A (en) | Accessing information content in a database platform using metadata | |
| CN114722137A (en) | Security policy configuration method and device based on sensitive data identification and electronic equipment | |
| US8260772B2 (en) | Apparatus and method for displaying documents relevant to the content of a website | |
| CN114356968A (en) | Query statement generation method and device, computer equipment and storage medium | |
| CN111190965A (en) | Text data-based ad hoc relationship analysis system and method | |
| CN114416733A (en) | Data retrieval processing method and device, electronic equipment and storage medium | |
| US20090077031A1 (en) | System and method for creating full-text indexes of patent documents | |
| CN107291951B (en) | Data processing method, device, storage medium and processor | |
| CN102902705A (en) | Locating ambiguities in data | |
| CN116340398B (en) | Method and system for deriving online custom report query | |
| CN116414854A (en) | Data asset query method, device, computer equipment and storage medium | |
| CN116541578A (en) | Asset digital multidimensional management method and system | |
| US20230394015A1 (en) | LIST-BASED DATA STORAGE FOR DATA SEARCHPeter | |
| CN113434585A (en) | Resource saving method and equipment | |
| CN111782958A (en) | Recommendation word determining method and device, electronic device and storage medium | |
| US20140317154A1 (en) | Heterogeneous data management methodology and system |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |