mysql分区 记录总数
This scenario was developed in response to a requirement to translate excel pivoting capabilities into Pandas code so that the automation developed could be applied on a large set of excel files. The raw data in the excel sheet primarily consists of contracts won in each fiscal year paired with their contract amounts. The requirement is to report on the number of contracts in each fiscal year that fall into “bins” or “buckets” of pre-defined contract amounts. Six bins of contract amounts were identified as bins of interest:
开发此方案是为了满足将excel数据透视功能转换为Pandas代码的要求，因此开发的自动化程序可以应用于大量excel文件。 Excel工作表中的原始数据主要由每个会计年度中赢得的合同及其合同金额组成。 要求是报告每个会计年度中属于预定义合同金额“桶”或“桶”的合同数量。 六个仓位的合同金额被确定为利息仓位：
1. <10M2. >=10M<25M3. >=25M<50M4. >=50M<75M5. >=75M<100M6. >=100M
1. <10平方米。 > = 10M <25M3。 > = 25M <50M4。 > = 50M <75M5。 > = 75M <100M6。 > = 100M
Three techniques have been provided below which create the output desired using a relational database, Pandas and Power BI- with usage of DAX functions.
下面提供了三种技术，它们使用关系数据库，Pandas和Power BI-以及DAX函数来创建所需的输出。
The data we will consider here is an excel sheet with three columns as seen in Fig 1. We would like to end up with a summarization shown in Fig 2.
我们将在这里考虑的数据是一个具有三列的excel表格，如图1所示。我们希望最终得到如图2所示的摘要。
Fig 1:
图。1：
Fig 2:
图2：
PostgreSQLWe will load the data directly from the excel file into the database using Import/Export functionality offered by pgAdmin. See section Setup Data below for details. After the load, the fiscalyearcontract table created, contains the data from the excel sheet.
PostgreSQL我们将使用pgAdmin提供的Import / Export功能将数据直接从excel文件加载到数据库中。 有关详细信息，请参见下面的设置数据部分。 加载后，创建的financialyearcontract表包含Excel工作表中的数据。
A single query can create the output with 1. Rows for each fiscal year, with count of contracts in each bin.2. Row totals (total number of contracts for each fiscal year).3. Column totals (total number of contracts for each bin of contract amount range) .The query contains common table expressions (CTE) which allow us to build upon each sub-query layer to group the data as well as create summarization.
单个查询可以创建输出，每个会计年度有1行，每个bin中有合同数。 行总计(每个会计年度的合同总数)3。 列总计(合同金额范围的每个bin中的合同总数)。查询包含公用表表达式(CTE)，使我们可以在每个子查询层上进行构建以对数据进行分组并创建汇总。
Postgres Querywith T as (select fy, sum(case when orig_amount < 10000000 then 1 else 0 end) as "<10M", sum(case when orig_amount >= 10000000 and orig_amount < 25000000 then 1 else 0 end) as ">=10M<25M", sum(case when orig_amount >= 25000000 and orig_amount < 50000000 then 1 else 0 end) as ">=25M<50M", sum(case when orig_amount >= 50000000 and orig_amount < 75000000 then 1 else 0 end) as ">=50M<75M", sum(case when orig_amount >= 75000000 and orig_amount < 100000000 then 1 else 0 end) as ">=75M<100M", sum(case when orig_amount >= 100000000 then 1 else 0 end) as ">=100M" from fiscalyearcontract group by fy), RT as (select CAST(fy as VARCHAR), "<10M", ">=10M<25M", ">=25M<50M", ">=50M<75M", ">=75M<100M", ">=100M" , "<10M" + ">=10M<25M" + ">=25M<50M" + ">=50M<75M" + ">=75M<100M" + ">=100M" as "Row Total" from T), CT AS (select 'Column Total', sum("<10M"), sum(">=10M<25M"), sum(">=25M<50M"), sum(">=50M<75M"), sum(">=75M<100M"), sum(">=100M"), sum( "Row Total") from RT) select * from RTunionselect * from CTorder by FY;Breaking up the query, we see that the subquery T, creates a new set of columns along with the fiscal years (fy). We add a one to an aliased column group when the query encounters an orig_amount belonging to that column group and a zero if it does not.Running just the T subquery will result in the table as seen below:
分解查询，我们看到子查询T创建了一组新列以及会计年度(fy)。 当查询遇到属于该列组的orig_amount时，我们为别名列组添加一个，如果不存在则为0.仅运行T子查询将生成下表，如下所示：
We can split up the construction of the row and column totals into two queries. The row total (RT) is simply the sum of the counts in each group and is selected as a new column along with the columns created in T.The column totals are computed in a query (CT) and then UNIONed to the result of RT to append the totals as the last row in the result.
我们可以将行和列总计的构造分为两个查询。 行总计(RT)只是每个组中计数的总和，并与在T中创建的列一起被选择为新列。列总计在查询(CT)中计算，然后UNIONed为RT的结果将总计作为结果的最后一行附加。
Power BI offers grouping and binning that can be used for creating histograms and for conditional formatting as explained here : https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-grouping-and-binning. The limitation we will overcome for this scenario is creating custom sized bins that are not uniform or unevenly sized with the help of DAX.
Power BI提供了可用于创建直方图和条件格式的分组和合并，如此处所述： https : //docs.microsoft.com/zh-cn/power-bi/create-reports/desktop-grouping-and-binning 。 在这种情况下，我们将克服的限制是在DAX的帮助下创建大小不一或大小不一的自定义大小的垃圾箱。
We begin by importing the excel file as a query (Fiscal Year Contract) into Power BI using Get Data-> Excel. We ensure that the datatype for Fiscal Year (fy) is Whole Number and for the contract amounts (orig_amount) is Decimal Number. Also for the field of fy we select Don’t Summarize in Column Tools.
我们首先使用获取数据-> Excel将Excel文件作为查询(会计年度合同)导入Power BI。 我们确保会计年度(fy)的数据类型为“整数”，合同金额(orig_amount)的数据类型为“十进制”。 另外，对于fy字段，我们在“列工具”中选择“不汇总”。
We proceed to define and create a range table using DAX’s DATATABLE. This nifty function allows us to create a query table specifying the column names and data types and insert rows in it in one shot.
我们继续使用DAX的DATATABLE定义和创建范围表。 这个漂亮的功能使我们可以创建一个查询表，以指定列名和数据类型，并在其中插入行。
Contract Amount Groups = DATATABLE( "Contract Amount Range", STRING, "Sort Order", INTEGER, "Start", INTEGER, "End", INTEGER, { {"<10M", 1, 0, 10000000}, {">=10M<25M", 2, 10000000, 25000000}, {">=25M<50M", 3, 25000000, 50000000}, {">=50M<75M", 4, 50000000, 75000000}, {">=75M<100M", 5, 75000000, 100000000}, {">=100M", 6, 100000000, 10000000000000} } )Now that we have our custom bins defined with a label and Start and End, a simple DAX can pick out the range that orig_amount in each row falls into. To store the range next to the orig_amount, create a calculated column in the Fiscal Year Contract query with the DAX shown below that works in two steps:
现在，我们已经定义了带有标签以及“开始”和“结束”的自定义垃圾箱，一个简单的DAX可以挑选出每行orig_amount所属的范围。 要将范围存储在orig_amount旁边，请使用以下所示的DAX在“会计年度合同”查询中创建一个计算列，该列可通过两个步骤工作：
1. It narrows down the Contract Amount Groups query to one row based on which category the amount falls in.2. It returns the range label using the VALUES function.3. If you find yourself extending this pattern of custom grouping to other applications, the thing to watch out for is that the amount must fall into only one group and so the comparison operators ≥ and < are to be employed with care.
1.根据金额所属的类别将“合同金额组”查询缩小到一行。 它使用VALUES函数返回范围标签3。 如果您发现将这种自定义分组模式扩展到其他应用程序，则需要注意的是，该数量只能属于一组，因此必须谨慎使用≥和<比较运算符。
This sets up the queries we can now use to visualize the entire data by fiscal year.
这将设置查询，我们现在可以使用这些查询按会计年度可视化整个数据。
In the Visualization tab, we select the matrix visualization. With this arrangement we end up with the desired result shown in the matrix below.
在“可视化”选项卡中，我们选择矩阵可视化。 通过这种安排，我们最终获得了所需的结果，如下表所示。
Data can be imported into Pandas directly from an excel or CSV file using the read_excel and read_csv functions. The code below shows intervals being created using IntervalIndex with the left side of the interval being closed (included value).
可以使用read_excel和read_csv函数将数据直接从excel或CSV文件导入到Pandas中。 下面的代码显示了使用IntervalIndex创建的间隔，该间隔的左侧已关闭(包含值)。
Pandas has a cut function that will apply these custom created bins on a column of values.
熊猫具有剪切功能，可将这些自定义创建的垃圾箱应用于一列值。
To display the bin labels along with the bins, we ‘zip’ the bins categories with the bin labels and create a dictionary that can be used to map the bin category that an amount belongs to.
要显示垃圾箱标签和垃圾箱，我们用垃圾箱标签“压缩”垃圾箱类别，并创建一个词典，可用于映射金额所属的垃圾箱类别。
Pandas’ groupby function can take a list of columns to group by. It returns a groupby object with details on each group. An aggregation function such as count when called on each group, returns the number of rows in that group for each of the columns. We thus get the number of contracts for each fiscal year and each bin category.
熊猫的groupby功能可以获取要分组的列的列表。 它返回一个groupby对象，其中包含每个组的详细信息。 在每个组上调用聚合函数(例如count)时，将为每个列返回该组中的行数。 因此，我们获得了每个会计年度和每个分类类别的合同数量。
All that remains to be done to the above result is to reset the index so that FY and CATEGORY_LABELS are columns so that we can pivot it with index = FY, column labels = CATEGORY_LABELS and values= CONTRACT_BINS. The final output is seen below. With the specification of margins=True, row and column totals come included in the pivot table created.
以上结果要做的全部工作就是重置索引，以使FY和CATEGORY_LABELS为列，以便我们可以使用index = FY，列标签= CATEGORY_LABELS和values = CONTRACT_BINS对其进行透视。 最终输出如下所示。 在margins = True的指定下，行和列的总数将包含在创建的数据透视表中。
CSV files can be imported into PostgresSQL (pgAdmin) , Pandas and Power BI.
CSV文件可以导入到PostgresSQL(pgAdmin)，Pandas和Power BI中。
To load the data into the database from an Excel file.
从Excel文件将数据加载到数据库中。
1. Create a table with the column names for the data to be imported in the Postgres database.
1.创建一个带有要在Postgres数据库中导入的数据的列名的表。
CREATE TABLE public.fiscalyearcontract( contract_id character varying(12) COLLATE pg_catalog."default", fy numeric(4,0), orig_amount numeric(23,4))TABLESPACE pg_default;2. Open the Excel file and save it as a CSV file.3. Clear the headings from the CSV file so that data begins from the first row.4. Select the table created above in pgAdmin, and then under Tools, select Import/Export.5. Toggle the switch over to Import, specify the full path to CSV file and click Ok
2.打开Excel文件并将其另存为CSV文件。3。 从CSV文件中清除标题，以便数据从第一行开始4。 选择上面在pgAdmin中创建的表，然后在“工具”下选择Import / Export.5。 将切换切换到“导入”，指定CSV文件的完整路径，然后单击“确定”。
To capture CSV data into a Jupyter notebook:
要将CSV数据捕获到Jupyter笔记本中：
import pandas as pddf = pd.read_csv('ContractsFiscalYearAmountBins.csv')To capture CSV data into a Power BI Query:Under Get Data select Text/CSV
要将CSV数据捕获到Power BI查询中： 在“获取数据”下，选择“文本/ CSV”
翻译自: https://medium.com/@sjtalkar/three-routes-partitioning-and-group-totals-f82a2fe4dc26
mysql分区 记录总数
